Help with Date intelligence

[epang]

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:

CurrentMTD = totalMTD( [Total Cases],DatesMTD('Cases'[Lodged Date]))

Previous MTD = CALCULATE([Total Cases], PREVIOUSMONTH(DATESMTD('Cases'[Lodged Date].[Date])))

Is there anything I can do to rectify this?  Note: I have turned on the auto time intelligence.  The filter is based on lodged date - Year. 

Many thanks.

[krishnakanth240]

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

[epang]

DimDate =

VAR StartDate = DATE(2020,1,1)

VAR EndDate = DATE(2030,12,31)

RETURN

ADDCOLUMNS(

    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?

[epang]

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? Thanks

[ShahRukhSameer]

Hi @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.

[Prince0011]

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 Enthusiast

[danextian]

Hi @epang 

It is a best practice to use a separate dates table and not auto datetime. Also, when you say previous MTD is that relative to the current date in the current month. For example if today is Aug 9 is the calcultion for July up to the 9th or the whole month?

[ShahRukhSameer]

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.

[epang]

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 ? 

[ShahRukhSameer]

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.