Forum Discussion
Calculate %Change
Hi All,
I am trying to calculate Total Sales %change over time selected by Date slicer.
The following image shows the details of the problem.
On selection of particular date in date slicers, %change shows the Total sales change back to that date selection.
I am trying many options on the internet but all options are shows Year over year %change and that logic is not applicable here.
So please help me out.
Thanks
Ok, I understand. If you always want to have the same number of dates in the previous period you could do something like this. But if you for example select a month with 31 days it will compare to the previous 31 days, not the previous month.
SalesSelectedPeriod = SUM(Sales[TotalSales])
SalesPreviousPeriod = CALCULATE(SUM(Sales[TotalSales]), FILTER(ALL(Sales[Date]), Sales[Date]<MIN(Sales[Date]) && Sales[Date]>=MIN(Sales[Date])-COUNT(Sales[Date])))
Change = Sales[SalesSelectedPeriod] - Sales[SalesPreviousPeriod]
Change % = DIVIDE([Change]; [SalesPreviousPeriod])
There might be problems if you don't have sales every day. In that case you should use a date dimension that has all dates, even days without sales.
4 Replies
To calculate a change in % you need two time periods to compare. In the slicer you select one time period, what do you want to compare it to? The total sales of all time? A "trick" when comparing time periods is to connect two date dimensions with inactive relations. Add two slicers to the report (using the two date tables). Then you create a few measures, something like this:
SalesPeriod1 = CALCULATE(SUM('Sales'[Amount]), USERELATIONSHIP('Sales'[Date], Date1[Date]))
SalesPeriod2 = CALCULATE(SUM('Sales'[Amount]), USERELATIONSHIP('Sales'[Date], Date2[Date]))
Change = [SalesPeriod2]-[SalesPeriod1]
Change % = DIVIDE(Change, SalesPeriod1)
Hi Anonymous,
Thank you for quick response and time.
I need to compare the period selected in the current slicers with the same last period back to that selected period.
In the below image, you can see the relative date slicer, in which we select
last 1 week, so %change is for the selected week and one week back of the selected week, same as if we select
last 1 month, so it displays %change for selected month and last one month back and so on
Ok, I understand. If you always want to have the same number of dates in the previous period you could do something like this. But if you for example select a month with 31 days it will compare to the previous 31 days, not the previous month.
SalesSelectedPeriod = SUM(Sales[TotalSales])
SalesPreviousPeriod = CALCULATE(SUM(Sales[TotalSales]), FILTER(ALL(Sales[Date]), Sales[Date]<MIN(Sales[Date]) && Sales[Date]>=MIN(Sales[Date])-COUNT(Sales[Date])))
Change = Sales[SalesSelectedPeriod] - Sales[SalesPreviousPeriod]
Change % = DIVIDE([Change]; [SalesPreviousPeriod])
There might be problems if you don't have sales every day. In that case you should use a date dimension that has all dates, even days without sales.