cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kunduz
Regular Visitor

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

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!

 

 

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@Kunduz,

 

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.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors