D A X: Formula to show all products in all months

[tiawy]

Hi guys

I am new to DAX and I am having a bit of trouble getting a dax formula to show all products even though they haven't been sold for a particular month..


My data consists of one table and looks like this. (Month is a calculated column)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P1[/TD]
[TD]4[/TD]
[TD]2014-1-1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P2[/TD]
[TD]3[/TD]
[TD]2014-1-1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P3[/TD]
[TD]2[/TD]
[TD]2014-2-1[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P1[/TD]
[TD]4[/TD]
[TD]2014-2-1[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P1[/TD]
[TD]2[/TD]
[TD]2014-1-1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P4[/TD]
[TD]3[/TD]
[TD]2014-1-1[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P1[/TD]
[TD]2[/TD]
[TD]2014-1-1[/TD]
[TD]February[/TD]
[/TR]
</tbody>[/TABLE]

I have made a calculated field called YTD that calculates year-to-date SUMS for the different products.
I also have made a calculated field called sum, that calculates the sum of products sold for the context specified in the PivotTable.


In the resulting PivotTable i get this result, if i for instance make a slicer on february:

February:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Product[/TD]
[TD]SUM[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P1[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


However what i want is this (I have highlighted what is different):

February:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Product[/TD]
[TD]SUM[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P1[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P2[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GB[/TD]
[TD]P3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]CAN[/TD]
[TD]P4[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



So when I have made a slicer on dates, and I choose for instance february it shows only the products sold in february. But i want i to show all the products that has YTD>0, and just put a 0 in the SUM for the products that hasen't been sold that month.



I know i could solve this by inserting rows in the data source with quantity 0 for the products that hasen't been sold in a month. But this seems like a bad hack - and since there a lots and lots of products this would result in many unnecessary rows...


I know how to solve it in SQL, but that is not an option here..I have tried to google it but havent found a solution to this in DAX.

Thank you for your help

 

[scottsen]

Do you have product and geography lookup tables? You kinda want that... and then you put fields from THOSE tables on your pivot and will likely get what you want....

 

[tiawy]

Thank you for your response.
The problem is however that there is no lookup tables. There is only one table - the one shown above.

That is what makes it challenging!

 

[Kazlik]

While not everything you needed this does most of it except when there is no product value for a month and country. If you add the County and Product to rows with Tabular layout and then add the following measures to the values. You can then add a slicer for Month


YTD Sales:=CALCULATE(SUM(Sheet1[Quantity]),ALL(Sheet1[Month]))
Month Sales:=TOTALMTD(SUM(Sheet1[Quantity]),Sheet1[Date])

 

[jafa1970]

This should help you in the right direction: ALL() Revisited « PowerPivotPro

 

[scottsen]

I don't think this is an ALL() problem.

Actually... it's kinda weird. Since you have a YTD measure, it should return a value for P2 in Feb... I'm actually wondering if that measure is working correctly. If you are using the builtin DATESYTD or similiar, those require a "real" date table, wonder if that's what is wrong?