• 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 2014-1-1 January GB P2 3 2014-1-1 January GB P3 2 2014-2-1 February GB P1 4 2014-2-1 February CAN P1 2 2014-1-1 January CAN P4 3 2014-1-1 January CAN P1 2 2014-1-1 February

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:

 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.

Thursday, December 11, 2014 3:52 PM

• 1. I don't understand what's going on in your fact table on the last row. The date is '2014-1-1', 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 time-intelligence 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.

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 '2014-1-1', 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 time-intelligence 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.

Monday, December 15, 2014 11:14 PM