Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Jay20024
Regular Visitor

Last year with year as number

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

 

1 ACCEPTED SOLUTION
sanalytics
Solution Sage
Solution Sage

@Jay20024 
Try this below formula for dynamic approach

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

 screenshot attached

sanalytics_0-1727691836405.png

 

Hope it helps

 

Regards

sanalytics

 

 

View solution in original post

6 REPLIES 6
sanalytics
Solution Sage
Solution Sage

@Jay20024 
Try this below formula for dynamic approach

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

 screenshot attached

sanalytics_0-1727691836405.png

 

Hope it helps

 

Regards

sanalytics

 

 

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

Capture001.PNG

 

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.

@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

 

Indeed, There was a error. It works nice !

tackytechtom
Super User
Super User

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)

 

tackytechtom_0-1727687526886.png

 

Let me know if this helps 🙂

 

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

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors