Creating measure filtered to one less than the value of a slicer

[Anonymous]

Hi All,

I am currently struggling with an issue whereby I am comparing years of data against each other. My table looks similar to the one below:

Year	Value
2018	14706.92
2019	13193.76
2020	10926.98

I also have a year slicer, to select which year I am viewing as my "Current Year". I want to create a new measure, which will be my "Previous Year Value", which is filtered to the year before the selected "Current Year". I then want to be able to calculate the difference between the current year and the previous year values.

It would look something like this:

Please note if 2020 was selected, all of these values would dynamically update.

If anyone has a smart solution please let me know.

[Ashish_Mathur]

Hi,

The first Table you have shared in your initial post looks like a visual that you have created (Table or matrix) not your actual input table.  Share your actual input table.  Does the actual input table have a proper Date column?

[Anonymous · Accepted solution]

Hi, I actually managed to get a solution myself using a date dimension table.

This table was calculated with: 

Date Dim = CALENDAR(MIN('Sales Table'[Date]), MAX('Sales Table'[Date]))

I then had my current year as a simple sum, and then my previous year value was calculated with the dax:

Volume Selected Yr-1 =

VAR LAGDATE = YEAR(MAX('Date Dim'[Date]))-1

RETURN

CALCULATE([Value], ALL('Date Dim'), ALL(Sheet1[Date]), YEAR(Sheet1[Date]) = LAGDATE)

This got me the solution, thanks for everyones help anyway!

[v-lid-msft]

Hi @Anonymous ,

We can try to create following three measures to meet your requirement:

Current Year Value = 
CALCULATE ( SUM ( 'Table'[Value] ) )

Previous Year Value = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( ALL ( 'Table' ), [Year] = SELECTEDVALUE ( 'Table'[Year] ) - 1 )
)

Delta = [Current Year Value] - [Previous Year Value]

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @Anonymous !

Have you tried Year to Date formula? 

[Anonymous]

Hi @Anonymous , could you please expand on this? Not sure how this can solve my problem

[Anonymous]

How do you have your data organized? Is been your total value calculated by a DAX formula? 

btw, this is what I refer to: https://docs.microsoft.com/es-es/dax/totalytd-function-dax