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 did some searching on this and I see what's causing it, but not sure how to fix it. I have a dataset and a date dimension table, both connected via a date field. My date table goes years in the future and there are no spaces but what I'm finding is that when I create a time intelligence calculation, it's scanning to the end of the date table, in this case 2033 and returns a zero. If I use a slicer and filter it down to cover the period of interest, the number is correct. It doesn't matter if we're talking about YTD, QTD, MTD, etc.
For example, let's say I want to to a TotalMTD and I write:
Solved! Go to Solution.
Hi @sfalk781
You've raised a good point. There could be multiple ways of addressing this.
The time intelligence functions all take the initial date filter context and modify it in some way.
In particular, the DATESMTD, DATESQTD, DATESYTD functions look at the latest date in the current filter context and produce a modifed date filter for the MTD/QTD/YTD period "as at" that latest date.
So, if your date table extends into the future to 2033 as in your example, and no filters are applied to DimDate, the latest date is in 2033 and you likely have no data during the YTD period ending some time in 2033.
A possible solution if you want your dates to be automatically limited to no later than the latest date in your fact table would be to write measures using this pattern:
MTD Measure =
VAR GlobalMaxDate =
CALCULATE ( MAX ( FactTable[Date] ), REMOVEFILTERS () )
RETURN
CALCULATE (
TOTALMTD ( [Total XYZ Reported], DimDate[Calendar Date] ), -- original measure
KEEPFILTERS ( DimDate[Calendar Date] <= GlobalMaxDate ) -- enforce upper-bound on Date
)
The definition of GlobalMaxDate could equally be something else that makes sense, such as TODAY().
Writing this for every measure might be tedious, so Calculation Groups would probably be a good idea to handle all time intelligence logic.
Those are a few ideas anyway.
Regards,
Owen
Thanks alot @OwenAuger for helping me with this. As a relatively new user, these problems can take forever to figure out. I'll research the calculation groups, but I was able to find two other methods in addition to what you suggested.
For the second bullet above, the code would look like this
Hi @sfalk781
You've raised a good point. There could be multiple ways of addressing this.
The time intelligence functions all take the initial date filter context and modify it in some way.
In particular, the DATESMTD, DATESQTD, DATESYTD functions look at the latest date in the current filter context and produce a modifed date filter for the MTD/QTD/YTD period "as at" that latest date.
So, if your date table extends into the future to 2033 as in your example, and no filters are applied to DimDate, the latest date is in 2033 and you likely have no data during the YTD period ending some time in 2033.
A possible solution if you want your dates to be automatically limited to no later than the latest date in your fact table would be to write measures using this pattern:
MTD Measure =
VAR GlobalMaxDate =
CALCULATE ( MAX ( FactTable[Date] ), REMOVEFILTERS () )
RETURN
CALCULATE (
TOTALMTD ( [Total XYZ Reported], DimDate[Calendar Date] ), -- original measure
KEEPFILTERS ( DimDate[Calendar Date] <= GlobalMaxDate ) -- enforce upper-bound on Date
)
The definition of GlobalMaxDate could equally be something else that makes sense, such as TODAY().
Writing this for every measure might be tedious, so Calculation Groups would probably be a good idea to handle all time intelligence logic.
Those are a few ideas anyway.
Regards,
Owen
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |