Calculatewith slicer applied

[Anonymous]

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?

[lbendlin]

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)

[Anonymous]

@lbendlinthanks for your response!

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.

[Anonymous · Accepted solution]

Hi @Anonymous ,

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.

[lbendlin]

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.