Pivot Count

[blue1406]

I am new to Powerpivot/DAX and can't figure out how to do the following.
I have an issues database that I imported into Powerpivot for dashboard reporting.
Each issue has a value for 'WE opened' and 'WE closed' (WE = week ending). I want to report on WE and how many issues have been opened/closed.
Here is a simplified example:

Source (Powerpivot table):[TABLE="width: 500"]
<tbody>[TR]
[TD]WE Opened[/TD]
[TD]WE Closed[/TD]
[/TR]
[TR]
[TD]4/6/2014[/TD]
[TD]4/13/2014[/TD]
[/TR]
[TR]
[TD]4/6/2014[/TD]
[TD]4/13/2014[/TD]
[/TR]
[TR]
[TD]4/13/2014[/TD]
[TD]4/13/2014[/TD]
[/TR]
</tbody>[/TABLE]


Pivot Table Report:
[TABLE="width: 500"]
<tbody>[TR]
[TD]WE[/TD]
[TD]Count Opened[/TD]
[TD]Count Closed[/TD]
[/TR]
[TR]
[TD]4/6/2014[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4/13/2014[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

If you normalise the data, life will be easier. So have two fields : WE and OpenCloseStatus. First three entries are the WE open dates and "Opened" next three are the WE closed dates and "Closed"

Maybe you can do that via SQL? Something like : SELECT [WE Opened] AS [WE], 'Opened' AS [OpenCloseStatus] FROM sourcedata UNION SELECT [WE Closed], 'Closed' FROM sourcedata