Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to calculate Delta value for selected month & selected month-1.
For Ex: if I have selected Aug-2020 as a month then it should be me delta value for Aug 2020 & Jul 2020 as shown below.
Please provide me the formula to calculate delta value.
Dimension is MonthYear (Format is DEC-2020)
Measure Sum(Sales).
@avinashelite , @eliran, @its_anandrjs , @MK_QSL , @marcus_sommer , @daniel_kusiak @rwunderlich , @Peter_Cammaert , @jonathandienst , @Taoufiq_Zarra
Thanks in Advance.
Hi @piyush_s11
If you want to get Delta for all month in Pivot table , try like below
Sum(Sales) - Before(Sum(Sales))
And also, if you want to get Delta based on selected month ,
In that case, you can hide/remove the dimension and try with below expression.
Sum(Sales)-Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))"},Month=,MonthYear=,Year=>}Sales)
Else,
You can enable conditional for MonthYear Dimension like
=GetSelectedCount(MonthYear)=0
and add expression as
If(GetSelectedCount(MonthYear)=1,
Sum(Sales)-Sum({<Date={">=$(=MonthStart(Max(Date),-1))<=$(=MonthEnd(Max(Date),-1))"},Month=,MonthYear=,Year=>}Sales), Sum(Sales) - Before(Sum(Sales)))
Hope it helps
Thanks for your quick reply.
I want the delta to be calculated as selected month & selected month -1
For Ex: if I have selected Aug-2020 as a month then it should be me delta value for Aug 2020 & Jul 2020 as shown below. (Only two months I should get like Aug 2020 & Jul 2020 Not all months).
Could you please help me with respect to above example.
Thanks