Previous interval date not working

[timzedel]

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.

[Mikelytics]

Hi @timzedel.

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

[timzedel · Accepted solution]

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]

@timzedel , 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