locked
Measure results limited by fact table dates instead of date table RRS feed

  • Question

  • Hello

    I am struggling with an issue whereby I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.

    Objective: Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).

    Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):

    StkOutCnt:=CALCULATE (
        COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
        FILTER (
            Calendar,
            [Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
        )
    )

    Below you can see the sub measures (circled in red) are giving results for all days in the calendar.

    Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).

    Would anyone have an insight as to why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?

    Many thanks

    Stefan

    Thursday, September 17, 2015 3:55 PM

Answers

  • If you could give us a bit more on your model (relations, measure definitions for the helper measures, data profiling), or a copy of sample data that reproduces your issue, that would be most helpful.

    In the meantime, try the following:
    StkOutCnt:=
    COUNTROWS(
      FILTER(
        VALUES(Calendar[DateKey])
        ,[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
      )
    )



    GNet Group BI Consultant

    • Marked as answer by Freddystyle Friday, September 18, 2015 7:06 AM
    Thursday, September 17, 2015 4:29 PM

All replies

  • If you could give us a bit more on your model (relations, measure definitions for the helper measures, data profiling), or a copy of sample data that reproduces your issue, that would be most helpful.

    In the meantime, try the following:
    StkOutCnt:=
    COUNTROWS(
      FILTER(
        VALUES(Calendar[DateKey])
        ,[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
      )
    )



    GNet Group BI Consultant

    • Marked as answer by Freddystyle Friday, September 18, 2015 7:06 AM
    Thursday, September 17, 2015 4:29 PM
  • Wow, thanks for such a quick and useful response! It works perfectly with the new formula.

    Do you have any insight as to what was going wrong in my previous formula? That would help me better understand the inner workings of DAX. Happy to provide you with any supporting materials. Note that the helper measures are working fine so pretty sure the issue is only in the top level formula.

    Thanks again!

    Friday, September 18, 2015 7:10 AM
  • Thinking further, one helper measure which may be related to the issue is the average monthly sales measure.

    Here are the component parts (note that tbl_P4 is the fact table with sales history and tbl_P1b is a full list of SKUs). I use an IF statement to suppress results beyond the last date in the fact table. Maybe there is a more elegant way to do this.

    Avg Monthly Sales Qty L12M@SKU:= DIVIDE([Sales Qty L12M],[Active Days L12M@SKU]/30,BLANK())

    Sales Qty L12M:=IF (
        MAX ( Calendar[DateKey] )
            > CALCULATE (
                MAX ( tbl_P4_Net_Sales_Actuals[Posting Date] ),
                ALL ( Calendar[DateKey] ),
                ALL ( tbl_P1b_SKU_Master )
            ),
        BLANK (),
        CALCULATE (
            [Net Sales Qty],
            DATESBETWEEN (
                Calendar[DateKey],
                NEXTDAY (
                    LASTDATE ( DATEADD ( Calendar[DateKey], -12, MONTH ) )
                ),
                LASTDATE ( Calendar[DateKey] )
            )
        )
    )
    Active Days L12M@SKU:=IF (
        MAX ( Calendar[DateKey] )
            < CALCULATE (
                MAX ( tbl_P4_Net_Sales_Actuals[Posting Date] ),
                ALL ( calendar ),
                ALL ( tbl_P1b_SKU_Master )
            ),
        SUMX ( tbl_P1b_SKU_Master, [Active Days L12M] ),
        BLANK ()
    )
    

    And here is the final measure which simply counts the number of days in the last 12 months or since a product first went on sale. Whichever is smaller. 

    Active Days L12M:=IF (
        NEXTDAY (
            LASTDATE ( DATEADD ( Calendar[DateKey], -12, MONTH ) )
        )
            < CALCULATE (
                MIN ( tbl_P4_Net_Sales_Actuals[Posting Date] ),
                ALL ( Calendar )
            ),
        CALCULATE (
            COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
            DATESBETWEEN (
                Calendar[DateKey],
                CALCULATE (
                    MIN ( tbl_P4_Net_Sales_Actuals[Posting Date] ),
                    ALL ( Calendar )
                ),
                LASTDATE ( Calendar[DateKey] )
            )
        ),
        CALCULATE (
            COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
            DATESBETWEEN (
                Calendar[DateKey],
                NEXTDAY (
                    LASTDATE ( DATEADD ( Calendar[DateKey], -12, MONTH ) )
                ),
                LASTDATE ( Calendar[DateKey] )
            )
        )
    )

    Thanks

    Stefan



    Friday, September 18, 2015 9:30 AM
  • I think the problem resides in my if statement:

    IF (
        MAX ( Calendar[DateKey] )
            > CALCULATE (
                MAX ( tbl_P4_Net_Sales_Actuals[Posting Date] ),
                ALL ( Calendar[DateKey] ),
                ALL ( tbl_P1b_SKU_Master )
            ),

    I should just use an ALL(tbl_P4_Net_Sales_Actuals) rather than an ALL on the date table and the SKU table. Right?

    This is what is giving me unexpected blank results at weekends etc... Not sure why I get blanks, but there must be something additional in the context suppressing the results on non-working days.

    Friday, September 18, 2015 9:59 AM
  • So, my first question, before diving into the logic of your measures up there, is why don't you just test MAX(Calendar[DateKey]) against the value of TODAY()?

    Also, this section in your measures:
    DATESBETWEEN (
                Calendar[DateKey],
                NEXTDAY (
                    LASTDATE ( DATEADD ( Calendar[DateKey], -12, MONTH ) )
                ),
                LASTDATE ( Calendar[DateKey] )
            )
    is needlessly complex. In you calendar table, create a field that contains an integer that increments by 1 for every month, never wrapping back around. I typically refer to this type of field as <time period>Sequential, e.g. MonthSequential. Thus, if your calendar starts in 2010, January 2010 is 1 and January 2011 is 13. In this way you can simplify your logic with the following:
    FILTER(
      ALL(Calendar)
      ,Calendar[MonthSequential] <= MAX(Calendar[MonthSequential])
        && Calendar[MonthSequential] > MAX(Calendar[MonthSequential]) - 12
    )

    or, if you want to roll on the day (e.g. May 16, 2014 - May 15, 2015):
    FILTER(
      ALL(Calendar)
      ,Calendar[DateKey] <= MAX(Calendar[DateKey])
        && Calendar [DateKey] > MAX(Calendar[DateKey]) - 365
    )



    GNet Group BI Consultant

    Friday, September 18, 2015 11:49 PM
  • Many thanks for these tips, I have implemented them and it helps performance.

    Also, I will avoid filtering on the fact table in the future. It is both slow and a real memory hog... Learning as I go...

    Wednesday, September 23, 2015 9:49 AM