cancel
Showing results for
Did you mean: Regular Visitor

Calculated measure for MAT, YTD, QTD to current date shows null

Hi Folks,

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]))
"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))  Community Support

To make things easy, you may try adding calculated tables.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Announcements 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.  