Last year with year as number

[Jay20024]

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

[sanalytics · Accepted solution]

@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

[Jay20024]

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.

[sanalytics]

@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

[Jay20024]

Indeed, There was a error. It works nice !

[tackytechtom]

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/

[Jay20024]

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