Calculate percent difference in values between two different slicer dates

[delsnermms]

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!

[amitchandak]

@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]

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?

[dax]

Hi @delsnermms , 

You also could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[manalla]

Hi @dax 

@delsnermms 

@amitchandak 

I have the same exact need, but the source is SSAS Tabular live connection. So SelectedValue , IN , Values DAX functions are not working.

Do we have any alternate when using SSAS Tabular for this sort of needs? calculating difference between a measure between two dates.

Thanks

Manoj

[amitchandak · Accepted solution]

@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))					

[delsnermms]

@amitchandak Thanks for the clarification. I was able to get the result to work with your formula if I use a date filter and select the start and end date within a single slicer (either mutliple select using CTRL or a between). But do you know if it is possible to work if the start and end dates are actually in two different slicers as shown in the picture?

[amitchandak]

@delsnermms 

You can have slicer directly on the Start date and end date and you will get two slicers. But Slicer in power bi has Range and List option, you have to choose in between those.