Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |