Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous interval date not working

Hi,   I am trying to create a measure that returns the previous year's value, using the following expression:    Previous Year Value = CALCULATE([Total Value], DATEADD('Table'[Year],-1,YEAR ) ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Mike,

     

    The YEAR column in 'Table' has years 2011 - 2022 formatted as date.

     

    Sample data here:

     

     

     

    I've just created a DimDate table using a simple 

    DimDate = SUMMARIZE('Sheet1 (2)','Sheet1 (2)'[Year])

    and have used the Year column in DimDate in the DATEADD expression, this seems to have solved the problem.

     

    However, I am confused as to why using the Year column in DimDate as opposed to Year column in the original Table made a difference - they were both formatted as date so what was the issue? 

     
     
Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi Anonymous.

 

For using Time Intelligence functions like DATEADD you need columns with dates. I assume you do not have that right? Can please show the dataset below with sample data?

 

Best regards

Michael

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Mike,

 

The YEAR column in 'Table' has years 2011 - 2022 formatted as date.

 

Sample data here:

 

 

 

I've just created a DimDate table using a simple 

DimDate = SUMMARIZE('Sheet1 (2)','Sheet1 (2)'[Year])

and have used the Year column in DimDate in the DATEADD expression, this seems to have solved the problem.

 

However, I am confused as to why using the Year column in DimDate as opposed to Year column in the original Table made a difference - they were both formatted as date so what was the issue? 

 
 
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , if you have date table then only then this will work (also you need have date table and use year from date table) 

    Previous Year Value = CALCULATE([Total Value],
    DATEADD('Date'[Date],-1,Day
    )
    )

     

    Using a separate date/year table

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s