locked
DAX formula to show all products in all months RRS feed

  • 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. 

    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 '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