Forum Discussion
Advocate IHelp with Date intelligence
Hi,
The main issue is likely that you're using the fact table date hierarchy (Cases[Lodged Date]) with Auto Date/Time.
I'd recommend creating a proper Calendar table and relating:
Calendar[Date] → Cases[Lodged Date]
Then use the Calendar date for all your time-intelligence calculations. For example:
Previous MTD =
CALCULATE(
[Total Cases],
DATESMTD(
DATEADD('Calendar'[Date], -1, MONTH)
)
)Similarly, use -1, QUARTER for Previous QTD and -1, YEAR for Previous YTD.
Once you have a proper Date table, I'd also turn off Auto Date/Time. This should resolve the blank previous-period values and make the MoM/QoQ/YoY calculations more reliable.
Solution SageHi,
Two issues: PREVIOUSMONTH needs a real date column, not DATESMTD wrapped inside it, and the auto date hierarchy can cause inconsistent behavior. Turn off auto time intelligence, build a proper Calendar table with an active relationship to Lodged Date, then use DATEADD instead:
Previous MTD = CALCULATE([Total Cases], DATEADD('Calendar'[Date], -1, MONTH))
Previous QTD = CALCULATE([Total Cases], DATEADD('Calendar'[Date], -1, QUARTER))
Previous YTD = CALCULATE([Total Cases], DATEADD('Calendar'[Date], -1, YEAR))
Then for % change:
MoM % = DIVIDE([CurrentMTD] - [Previous MTD], [Previous MTD])
If still blank, check the Calendar-to-Lodged Date relationship is active.
💡 Helpful? Give a Kudos 👍 — keep the community growing. |