cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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.

 

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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)

@lbendlinthanks for your response!

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

Thomdh_0-1638175458109.png

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.

 

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

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

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

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

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (1,879)