Answered by:
DAX formula to show all products in all months
Question

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)Country Product Quantity Date Month GB P1 4 201411 January GB P2 3 201411 January GB P3 2 201421 February GB P1 4 201421 February CAN P1 2 201411 January CAN P4 3 201411 January CAN P1 2 201411 February
I have made a calculated field called YTD that calculates yeartodate 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:
Country Product SUM YTD GB P1 4 8 GB P3 2 2 CAN P1 2 4
However what i want is this (I have highlighted what is different):
February:
Country Product SUM YTD GB P1 4 8 GB P2 0 3 GB P3 2 2 CAN P1 2 4 CAN P4 0 3
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 have tried to google it but havent found a solution to this in DAX.
Thank you for your help
Thursday, December 11, 2014 3:52 PM
Answers

1. I don't understand what's going on in your fact table on the last row. The date is '201411', but the month is 'February'. I am working on the assumption this is an error. If I am wrong, please explain what's going on.
2. You need a date table. There's no good reason not to have a date table, and if you intend to use built in timeintelligence functions, it is a pretty strict requirement. If you use time intelligence functions based on a table other than a properly formatted date table, the results cannot be guaranteed. If you need help making a date table, let me know. I have an Excel model DimDate and a SQL script that I use for all of my own DimDates. Including a date table solves your problem when we rewrite your [Quantity YTD] as follows:
Quantity YTD:=TOTALYTD( [Sum of Quantity], DimDate[Date] )
Note: it is considered a best practice to always fully qualify your column references by preceding the column name with the table name.
Addenda: A proper date table needs a column of contiguous dates starting on Jan 1 for the first year you have data and ending on Dec 31 for the last year you have data. Make sure to mark it as a date table in the Power Pivot window under the 'Design' tab.
 Proposed as answer by Ed Price  MSFTMicrosoft employee Wednesday, December 31, 2014 2:40 AM
 Marked as answer by Michael Amadi Wednesday, January 7, 2015 9:35 AM
Monday, December 15, 2014 11:14 PM
All replies

Can you share the measures you are currently using?Thursday, December 11, 2014 4:28 PM

Hello
My measures are
Sum of quantity:= SUM([Quantity])
Quantity YTD:= TOTALYTD([Sum of quantity];[Date])
Friday, December 12, 2014 1:34 PM 
1. I don't understand what's going on in your fact table on the last row. The date is '201411', but the month is 'February'. I am working on the assumption this is an error. If I am wrong, please explain what's going on.
2. You need a date table. There's no good reason not to have a date table, and if you intend to use built in timeintelligence functions, it is a pretty strict requirement. If you use time intelligence functions based on a table other than a properly formatted date table, the results cannot be guaranteed. If you need help making a date table, let me know. I have an Excel model DimDate and a SQL script that I use for all of my own DimDates. Including a date table solves your problem when we rewrite your [Quantity YTD] as follows:
Quantity YTD:=TOTALYTD( [Sum of Quantity], DimDate[Date] )
Note: it is considered a best practice to always fully qualify your column references by preceding the column name with the table name.
Addenda: A proper date table needs a column of contiguous dates starting on Jan 1 for the first year you have data and ending on Dec 31 for the last year you have data. Make sure to mark it as a date table in the Power Pivot window under the 'Design' tab.
 Proposed as answer by Ed Price  MSFTMicrosoft employee Wednesday, December 31, 2014 2:40 AM
 Marked as answer by Michael Amadi Wednesday, January 7, 2015 9:35 AM
Monday, December 15, 2014 11:14 PM