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
)
)
 
However, when I visualise this in a table, the measure does not work as intended - it only gives the aggregated total, but not the year by year (e.g. 2011 should be blank, 2012 should be 364341500 (2011's value) etc.).
 

 

Can someone please explain what is wrong with my DAX? Power BI is not picking up any errosrs.
 
 
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? 

     
     

3 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident 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
        Super 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