Dynamic slicers for time intelligence not working as expected

[AdrienneB]

I have a golden dataset that serves as the basis for many reports and for adhoc data exploration. It has a star schema with one FACT table. My problem is with creating dynamic date slicers for time intelligence.

The FACT table has data for month-end dates and current MTD weekday dates only, that is not continuous dates. All measures are in a measures table, FACT table hidden from dataset users. We have a designated date table with continuous dates from 2015 through 2024, which more than the time frame of the FACT table.

Using DAX, we created a Relative Time table that has  business relevant filters defined, such as Current Date (not sysdate but latest date from FACT table), Last Audited Month (again per FACT table filters), etc. This table allows for dynamic slicers on report pages.

Recently we were asked to add time intelligence calculations using calculation groups to this dataset, which was not a problem. We created items like: Selected Month, Prior Month, MoM, MoM%, Selected QTD, Prior QTD, QoQ, QoQ%, etc. See below picture.  The definitions are based on the date table as it has continuous dates. This all works as long as the filter on the report is based on the date table. However, it does not work with the nested Relative Time, 'Date Table' As of date combined filter's dynamic filtering.

The problem is that if a Relative Time is selected from the filter, the filtering does not work. Shows the proper calculations for every date in the FACT table, no filtering. If a date is selected then it filters for that date but then the slicer is not dynamic, following month has to be reset. Our requirement is to have the time intelligence calculations work with the dynamic filters.

Here is the sample DAX from the time intelligence calculation group:

CALCULATIONGROUP '0 Relative Time Calculations'[Time Measures]

    CALCULATIONITEM "Selected Month" =

        CALCULATE(

                    SELECTEDMEASURE()

                    ,CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH )

                )

        Ordinal = 0

    CALCULATIONITEM "Prior Month" =

        CALCULATE(

                    SELECTEDMEASURE()

                    , PREVIOUSMONTH('0 Date Table'[As Of Date])

                    , CROSSFILTER( '0 Date Table'[As Of Date], '0 Relative Time'[ASOF_DT], BOTH )

                )

        Ordinal = 1

[amitchandak]

@AdrienneB , You should use date table.

Note dates*/Total* takes the last selected date to calculate mtd, qtd ytd

Previous* take the first date. So if select 2 months range. MTD will give the last month and previousmonth will give 3rd last month

Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

I always prefer date table and time intelligence function

example

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

or create all measure and use field parameters

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0