cancel
Showing results for
Did you mean:

## Calculatewith slicer applied

Hello,

I would like to calculate our margin year over year. I had a formula that worked for the current year.

CurrentMargin =
var som = SUM(Table[salesExVAT]) - SUM(table[costprice])
return DIVIDE(som,SUM(table[SalesExVAT]))

Last year margin =

var LYmargin = CALCULATE(SUM(Table[costprice]),YEAR(Table[Date])=YEAR(TODAY())-1)
return DIVIDE(LYmargin,[PrevYear]) (PrevYear = sum of turnover last year)

Margin YOY =
var som = [CurrentMargin]-[LastyearMargin]
return DIVIDE(som,[LastyearMargin])

This gave the result that I needed. But I have added a slicer on the top of my page with year which can be selected.
Now I need the or a formula to auto adjust to the year selected. So if I choose 2021, it has to look to that previous year (2020), but when I select 2019, it has to look to that previous year.

What kind of DAX formula is needed to create this?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Thomdh ,

You want to use the slicer to select the year to return the value of the previous year of the selected year, right? Then you need a separate year table, which has nothing to do with the main table.

Here's my example.

Sample data:

1.Create a measure.

``PreviousYear Value = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=SELECTEDVALUE('Table (2)'[Year])-1))``

2.Create a slicer with the year field from Table(2). In the table visual, the year field is from Table. When I selcet year 2020, it shows the value in 2019.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4

You may be slightly overcomplicating this

LastYearMargin = CALCULATE([CurrentYearMargin],SAMEPERIODLASTYEAR(Table[Date]))

(although this will work more reliably if you have a true calendar table)

I can definitely try that. But will that also work with a slicer like this?

Because if I use the measure [CurrentYear] it will always return 2021 right? Even if the slicer 2020 is selected.

No. [CurrentYear] has nothing to do with 2021. It is based on the current filter context for each element of your visual.

You might consider renaming the measure to reduce the ambiguity.

Hi @Thomdh ,

You want to use the slicer to select the year to return the value of the previous year of the selected year, right? Then you need a separate year table, which has nothing to do with the main table.

Here's my example.

Sample data:

1.Create a measure.

``PreviousYear Value = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Year]=SELECTEDVALUE('Table (2)'[Year])-1))``

2.Create a slicer with the year field from Table(2). In the table visual, the year field is from Table. When I selcet year 2020, it shows the value in 2019.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

#### Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

#### Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (1,879)