Hi Folks,
I was trying to solve this for a week now and couldn't. Please help.
I am calculating PVM impact (Price impact shown in the example below) for MAT, YTD, QTD and MTD to current date.
Everything is ok, except for categories where sales (value) for current date is null. For example:
My Price impact formula is = (Price CY-Price LY)*Volume LY
variables Price CY, Price LY and Volume LY IS NOT NULL for MAT to date 01.12.2017, however
Price CY, Price LY and Volume LY IS NULL for particular date 01.12.2017, which means in this particular date there were no sales.
In this case I am getting null for PVM impact for MAT (YTD, QTD) to particular date as well.
I am using formulas as below:
04 PVM Impact = VAR Mix = sumx(summarize(Report;Report[SKU];Report[segment];Report[Companies]);if([02 Value LY]=0;[02 Value CY]))
VAR Price = sumx(ADDCOLUMNS(summarize(Report;Report[SKU];Report[segment];Report[Companies]);
"price";if([03 Volume CY]=0;0;if([03 Volume LY]=0;0;[03 Volume LY]*([03 Price CY]-[03 Price LY])));
"volume";if([03 Volume CY]=0;0;if([03 Volume LY]=0;0;[03 Price CY]*([03 Volume CY]-[03 Volume LY]))));
if([03 Price CY]/[03 Price LY]>2;if(ABS([price])>ABS([volume]);[price]+[volume];0);[price]))
VAR Volume = sumx(summarize(Report;Report[SKU];Report[segment];Report[Companies]);
if([03 Volume CY]=0;0;if([03 Volume LY]=0;0;[03 Price CY]*([03 Volume CY]-[03 Volume LY]))))
Return if(HASONEFILTER('PVM order'[PVM]);Switch(max('PVM order'[order]);1;Price;2;Volume;3;Mix);Price+Volume+Mix)
02 Value CY = VAR MAT = CALCULATE([01 measures]; DATESINPERIOD(Dates[Date];min(Dates[Date]);-1;YEAR))
VAR YTDval = TOTALYTD([01 measures];Dates[Date];all(Dates[Date]))
VAR QTDval = CALCULATE([01 measures]; DATESINPERIOD(Dates[Date];min(Dates[Date]);-3;MONTH))
Return SWITCH(max(Periods[Order]);1;MAT;2;YTDval;3;QTDval;4;[01 measures])
02 Value LY = CALCULATE([02 Value CY];DATEADD(Dates[Date];-1;YEAR))
Thank you for your support!
