Forum Discussion
epang
Advocate I
Advocate IHelp with Date intelligence
Hi team, I have created Total cases for the Current MTD , Current QTD and Current YTD. The data shows correctly However, when I create previous MTD, previous QTD and previous YTD. The data are a...
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.
