DAX YoY Price change issue if no new price available

[hansolu]

Hello

I need to compare the changes for a price over the last year.
I created a measure for each year to calculate the price (Y2022Price, Y2021Price) and then the YoY Measure to compare the change

This works for each single part number but gives me an error in the total if some parts are new (no price in 2021) or some parts are stopped(no price in 2022).

The measure Y2022Price is only used for the purpose to get the YoY change. So its fine to show only values where in 2022 and 2021 a price is available

I tried to modify the measure a bit

Please Login or Register  to view this content.

but this doesnt work.

Target is to see the changes YoY for each Partnumber and in total for all

Any ideas?
thanks a lot

[Bo_Ry]

The proper way to do this is to create date column and a real calendar table to use Time intelligence functions; eg SAMEPERIODLASTYEAR or DATESINPERIOD

But this model I'll use

2021P

=VAR T = SUMMARIZE(tblPrice,tblPrice[Number],"P",if([Y2022Price],[Y2021Price]))
return SUMX(T,[P])


2022P
=VAR T = SUMMARIZE(tblPrice,tblPrice[Number],"P",if([Y2021Price],[Y2022Price]))
return SUMX(T,[P])

Yoy
=if([2021P],DIVIDE([2022P],[2021P])-1)

[hansolu]

Hi

thanks a lot for the help
I was thinking about the proper date previously. I do have another case where the price changes during the year and i need to calculate the total sales considering this situation.

Guess using the eg SAMEPERIODLASTYEAR or DATESINPERIOD is better.

I just modified the DM and created out of the year a calender for it.

How would you do the YoY for this DM?

thanks a lot

[hansolu]

HI


I was thinking and studied a bit. Now I added a datetable and also did the pricing for sales and BOM for different periods.
Everything is working fine. Time Intelligence is really easier to use , thanks for the hint.
HOwever, 2 issues

Yellow Cells:
For my measure YoY SalesPrice % I do not see the totals, same for the measure DeltaSalesYoY(1yr).

Green Cells:
Another issue is I want to compare one year (e.g. 2022) and calculate based on January 2020 sales price the impact until now.
So i have a measure DeltaSalesYoY(2yr). Issue is I dont know why the summary for the years are wrong and how to make the year selection dynamic (based on the hightest year in the chart)

Any ideas how I can fix this?

[Bo_Ry]

Issues Measures are calculated based on

SpotPriceSales

Please Login or Register  to view this content.

Since SpotPriceSales give blank for subtotal rows, other Measures refer to SpotPriceSales also give blank


You need to use if(HASONEVALUE()) to get correct SpotPriceSales on subtotal row before going to the next calculation.

I don't know how you want to calculate SpotPriceSales yearly.

[hansolu]

Hi
ahh, I see.
I modified and Simply want to return the average of all salesprices for that year (or better for that grouping like year or quarter)

Please Login or Register  to view this content.

Now the subtotal is shown (wrong value but at least one value)

Actuall its most of the subtotals which are wrong.
SpotpriceSales (average over year is fine),
YoYSalesPrice%,(average is fine)
DeltaSalesYoY(1yr) (must be some of all single monthly results)
DeltaSalesBase(2020-01) (must be some of all single monthly results)

Most of them are all linked to the SpotpriceSales i guess.

Would be great to fix this.

thanks a lot

[Bo_Ry]

Too many fact table cause too complicate DAX

Try merge 2 fact tables in to 1 table

[hansolu]

HI

thanks a lot, works like a charm, thanks a lot.

I updated the model to add also the supplier and usage of each component of the supplier part. Works also fine.
I uploaded the new file, just as reference if someone is interessted.

One question, You made 2 measuers, YoYSale and YoYSale1. I tried to combine them as YoYSale1 is what I want. The result show wrong values then.

Not a big issue, can just hide the measure in the model.


Thanks a lot for your help