topic Last year with year as number in DAX Commands and Tips

Last year with year as number

Jay20024 — Mon, 30 Sep 2024 08:14:27 GMT

Hi all,

Got a table with year in number format,items and amounts

2020 | item01 | 10000 €

2020 | item02 | 3244 €

2021 | item 01 | 2312 €

2021 | item 02 | 6435 €

2022 | item 01 | 4334 €

2022 | item 02 | 5634 €

A measure = sum(table[amount])

A filter on the year = 2022

Measure show : 9968 in a smart card

I'd like to show in another smart card the 2021 amount ( 8747) by calculating something like

Calculate [measure], [year]-1

Thank you

Re: Last year with year as number

tackytechtom — Mon, 30 Sep 2024 09:12:34 GMT

Hi Jay20024 ,

You have a few options here. You could add a filter on the visual in the filter pane as well. Here you can even do relative filters.

Or you could use a measure like this:

Measure 2021 = CALCULATE([Measure], 'Table'[Year] = 2021)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Last year with year as number

sanalytics — Mon, 30 Sep 2024 10:24:21 GMT

Jay20024
Try this below formula for dynamic approach

PY = CALCULATE( [Sum Value], FILTER( ALL( Source[Year] ), Source[Year] = MAX( Source[Year] ) -1 ) )

screenshot attached

Hope it helps

Regards

sanalytics

Re: Last year with year as number

Jay20024 — Mon, 30 Sep 2024 11:49:47 GMT

I need a get the result for each year to display them in a pivot table like that

So I can add them to a scorecard, make calculations. I need a behavior like previousyear

So each time a select a year in the filter, I get the line related to the selected year, total amount and total amount LY. Many thanks for your help.

Re: Last year with year as number

sanalytics — Mon, 30 Sep 2024 11:28:48 GMT

Jay20024
I suggest you to replicate above all things in power bi and check. it seems that you have not tried or you have not understood the code.
MAX is respecting the filter context which is coming from your slicer as well.
Check from your end once.

Regards

sanalytics

Re: Last year with year as number

Jay20024 — Mon, 30 Sep 2024 11:38:13 GMT

For the year i need to take in account the sected value of the filter. Thanks

Re: Last year with year as number

Jay20024 — Mon, 30 Sep 2024 11:56:06 GMT

Indeed, There was a error. It works nice !