Date Slicer breaks my time intelligence DAX

[Anonymous]

I have 2 DAX measures:

Backlog (h) =
CALCULATE (
    SUM ( 'dw VW_WorkOrderFactuom'[Work Order Duration Remaining (h)] ),
    'dw VW_WorkOrderFactUOM'[In Backlog] = TRUE (),
    'dw VW_WorkOrderFactUOM'[Live Backlog] = TRUE (),
    NOT ( 'dw WorkOrderDim'[Work Purpose] IN { "MO", "WS", BLANK () } )
)

Backlog (h) 7 Days Ago NEW =
CALCULATE ( [Backlog (h)], DATEADD ( 'dw DateDim'[Date], -7, DAY ) )

1. Shows 'Backlog (h)' on the December 3 = 51,293

2. Shows 'Backlog (h)' 7 days previous on November 26 =52,602

3. Shows 'Backlog (h) 7 Days Ago NEW' = 52,602

Everything is working as expected.

If I select the grid row for December 3:

1. Shows 'Backlog (h)' = 51,293

2. Shows 'Backlog (h) 7 Days Ago NEW' = 52,602

Everything is working as expected

If I use my Date Slicer to select only December 3rd:

1. Shows Date Slicer December 3rd selected

2. Shows Backlog (h) = 51,293

3. Shows 'Backlog (h) 7 Days Ago NEW' as Blank

'Backlog (h) 7 Days Ago NEW' breaks when I use a date slicer, why?

I believe there is something wrong with the DAX in 'Backlog (h) 7 Days Ago NEW' that breaks when I use my date slicer.  I'm thinking it has something to do with filtering and could require am 'ALL', 'ALLSELECTED' or 'ALLEXCEPT' type of function.

Any help is greatly appreciated.

Steven

[daxer-almighty · Accepted solution]

If you mark the 'dw DateDim' table as the date table in the model, it should all work. Have you marked the table as such? I can see that you join the tables on DimDateKey which I believe is an int key. In this case you have to mark the table as a DATE table and select a true date column as the key in it. Then everything will be working fine.

[Anonymous]

Thanks for a great solution.  Much appreciated

[CNENFRNL]

Hi, @Anonymous , indeed, the reason for the issue lies in the hierarchical date slicer; and it's truly hidden. But it's revealed here (hover the mouse on the funnel icon of table viz)

As to your measure,

Backlog (h) 7 Days Ago NEW =
CALCULATE ( [Backlog (h)], DATEADD ( 'dw DateDim'[Date], -7, DAY ) )

DATEADD() evaluates to 'dw DateDim'[Date]=2020/11/26, which overrides 'dw DateDim'[Date]=2020/12/3 (that's why all works when the 'dw DateDim'[Date] is used as slicer at the top level); but another 2 filters still lingering there unexpectedly, as shown above, 'dw DateDim'[Year]=2020 and 'dw DateDim'[Month Name]=Dec!

The measure only evaluates with all these filters together; unfortunately these filters filter out all dates down to blank.

Enjoy filters, enjoy DAX! 😁

[Anonymous]

Excellent explanation of the problem.  Thanks for your help 🙂

[CNENFRNL]

Hi, @Anonymous , it's not surprising such seemingly same slicing brings about different results as measures are super sensitive to any changes in the evaluation context.

I doubt that the date columns in the table viz and the slicer are the same. In fact, it requires to take a close look at the data model and vizs to troubleshoot.

[Anonymous]

Hi CNENFRNL,

Thanks for the prompt reply it is much appreciated.  I am including screenshots of the relationship between my DateDim table and my WorkOrderFactUOM table:

Screenshot of my table viz using 'dw DateDim'[Date]

Screenshot of hierarchical slicer using 'dw DateDim'[Date], 'dw DateDim'[Year No] and 'dw DateDim'[Month Name]

Screenshot of a Non hierarchical slicer using 'dw DateDim'[Date].  Bizararrely, this works fine.  It appears to be an issue when using the hierarchy slicer