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.

krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
1 month ago

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