Forum Discussion

delsnermms's avatar
delsnermms
Regular Visitor
6 years ago
Solved

Calculate percent difference in values between two different slicer dates

I'm new to Power BI and need help with a specific calculation. I'm building a dashboard that has a number of different slicers. I want to calculate the percent difference between two values selected in a "Start Date" and an "End Date" slicer. All of the data is in a single table, something like this:

 

Date                        Sales

Jan 31, 2020            10

Feb 28, 2020            20

Mar 31, 2020           30

Apr 30, 2020            40

 

I have a slicer to select Start Date (i.e. Jan 31, 2020). I also have a slicer for End Date (i.e. Mar 31, 2020). How do I get the values from the selected Start Date and End Date from the table to do the calculation of (30 - 10)/10 and then display the result in a card?

 

Any help is appreciated!

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    delsnermms 

    You need to have them as the variables in the formula. If you do not have a timestamp in date, then remove .Date

     

    Percent Change = 					
var _max = maxx(Locations,Locations[Forecast Date].[Date])
var _min = MINX(Locations,Locations[Forecast Date].[Date])
 return
CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=_max))-CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=_min))

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    delsnermms ,Prefer using a date table

    measure =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])

return
CALCULATE(SUM(Table[Sales]), FILTER(all('Date'), 'Date'[Date]=_max)) - CALCULATE(SUM(Table[Sales]), FILTER(all('Date'), 'Date'[Date]=_min))

/////////////  OR 
measure =
var _max = maxx('Table','Date'[Date])
var _min = Minx('Table','Date'[Date])

return
CALCULATE(SUM('Table'[Sales]), FILTER(all('Table'), 'Table'[Date]=_max)) - CALCULATE(SUM('Table'[Sales]), FILTER(all('Table'), 'Table'[Date]=_min))

     

     

    • delsnermms's avatar
      delsnermms
      Regular Visitor
      6 years ago

      Thanks amitchandak. It doesn't seem to work. Here's what I created:

      var_max = maxx(Locations,Locations[Forecast Date].[Date])
      var_min = MINX(Locations,Locations[Forecast Date].[Date])
       
      Percent Change = CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=[var_max]))-CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=[var_min]))
       
      I just get a value of 0 for the Percent Change. Sugguestions on where I've gone wrong?

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        delsnermms 

        You need to have them as the variables in the formula. If you do not have a timestamp in date, then remove .Date

         

        Percent Change = 					
var _max = maxx(Locations,Locations[Forecast Date].[Date])
var _min = MINX(Locations,Locations[Forecast Date].[Date])
 return
CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=_max))-CALCULATE(SUM(Locations[Cost Index]),FILTER(ALL(Locations),Locations[Forecast Date].[Date]=_min))