Forum Discussion

epang's avatar
epang
Advocate I
13 days ago

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 a...
Need Help
Tips and Tricks
ShahRukhSameer's avatar
ShahRukhSameer
Helper V
13 days ago

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's avatar
epang
Advocate I
13 days ago

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's avatar
    ShahRukhSameer
    Helper V
    13 days ago

    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.