Forum Discussion

epang's avatar
epang
Icon for Advocate I rankAdvocate I
1 month ago
Solved

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
    1 month 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.

ShahRukhSameer's avatar
ShahRukhSameer
Icon for Continued Contributor rankContinued Contributor
1 month 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
Icon for Advocate I rankAdvocate I
1 month 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
    Icon for Continued Contributor rankContinued Contributor
    1 month 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.