Forum Discussion
Help 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 all blank.
The date value i use is in the fact table Lodge Date with date hiearchy. I would like to calculate the MoM% change , QonQ% change and YTD% change.
Here is the formula below:
11 Replies
Hi epang
Issue is PREVIOUSMONTH inside DATESMTD and auto time intelligence which creates hidden per column date table which will not work properly with some of time intelligence functions where you need seperate Date table
You can turn off auto date/time, create a seperate Date table and provide relationship between Date column(Date Table - Dimension) to Lodged Date column(Fact table) and use DATEADD
Previous MTD = CALCULATE([Total Cases], DATEADD(DATESMTD('DimDate'[Date]), -1, MONTH))
Similar pattern for QTD/YTD
- DimDate =VAR StartDate = DATE(2020,1,1)VAR EndDate = DATE(2030,12,31)RETURNADDCOLUMNS(CALENDAR(StartDate, EndDate),"Year", YEAR([Date]),"Month Number", MONTH([Date]),"Month Name", FORMAT([Date], "MMMM"),"Month Short", FORMAT([Date], "MMM"),"Quarter Number", QUARTER([Date]),"Quarter", "Q" & QUARTER([Date]),"Year-Month", FORMAT([Date], "YYYY-MM"),"Year-Quarter", YEAR([Date]) & "-Q" & QUARTER([Date]),"Day Number", DAY([Date]),"Day Name", FORMAT([Date], "DDDD"),"Day Short", FORMAT([Date], "DDD"),"Week Number", WEEKNUM([Date]))i have created the following dim date table. After that, when I join the date to lodge date, the relationship becomes inactive. Any clues to fix that?
After I create the table and join the date table field and the date in the fact table, the relationship is now active, but when i use the suggested formula to create previous Month to Date, it returns as blank.
Previous MTD = CALCULATE([Total Cases], DATESMTD(DATEADD(DimDate[Date],-1,MONTH)))Any clues to fix that?? I suspect it may be due to the format of the date. for example, the lodged date is recorded as 1/7/2026 11:03am. the date field is 1/7/202612:00am. will that cause power bi failure to display the data at all? ThanksHi epang,
Yes, the time portion could be causing the issue here.
Your DimDate[Date] has something like 01/07/2026 12:00 AM, while the fact table has 01/07/2026 11:03 AM. Although they are the same calendar date, Power BI treats them as different DateTime values.
I would create a date-only column in the fact table and use that for the relationship:
Lodged Date Only =
DATEVALUE(FactCases[Lodged Date])Then create the relationship:
DimDate[Date] → FactCases[Lodged Date Only]
with DimDate on the 1 side and the fact table on the many side.
Also make sure DimDate[Date] is set to the Date data type and mark DimDate as the Date table.
Your Previous MTD measure can then be:
Previous MTD =
CALCULATE(
[Total Cases],
DATEADD(DimDate[Date], -1, MONTH)
)One other thing I'd check is what you're using in the slicer or visual. Ideally, use DimDate[Date] rather than FactCases[Lodged Date] for your date filtering.
For example, if you're looking at July 1–15, the Previous MTD should return June 1–15.
If it is still blank after removing the time portion, I'd check whether there are any other relationships between the fact table and DimDate, such as Created Date, Closed Date, etc. Multiple date relationships can sometimes cause one of them to be inactive.
So I'd start by creating the date-only column and testing the measure again. That is the first thing I'd check in this case.
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.
I tried to link the lodge date to date table. the lodge date is currently displayed as like 01/07/2026 11:06am and date will be displayed as 01/07/2026 12:00am. does it impact the join the displaying the result ?
Hi epang,
Yes, that will affect the relationship. Since Lodged Date contains a time value (11:06 AM) while your Calendar date is at midnight (12:00 AM), they are technically different values and won't match.
You can create a separate date-only column from Lodged Date and use that for the relationship:
Lodged Date Only = DATEVALUE('Cases'[Lodged Date])
Then create the relationship:
Calendar[Date] → Cases[Lodged Date Only]
Keep the original Lodged Date column for the time/details, but use the date-only column for the relationship and time-intelligence calculations.
Hi,
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.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards,
Prince Singh | Data Science & Microsoft Fabric EnthusiastHi epang,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi epang,
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
