topic Re: Calculate YOY by ignoring the lowest granularity? in DAX Commands and Tips

Calculate YOY by ignoring the lowest granularity?

alaynanich — Fri, 24 May 2024 22:24:55 GMT

Hi PBI community,

So I have some sales data that comes in the below format. The Periods TY and LY means "This Year" and "Last Year", and I'm trying to find the YOY sales. The measure usually works but I think the transaction ID is confusing PBI and it's not calculating the difference. Is there any way to edit my DAX to ignore transaction id and get yoy for the skus? I only need transactions for distinct counts (my real data has a product hierarchy assoaciated with the sku so i need distinct counts and YOY sales for those too).

Sales YOY =

var ty = CALCULATE(SUM('Table'[sales]),'Table'[Period]="TY")

var ly = CALCULATE(SUM('Table'[sales]),'Table'[Period]="LY")

RETURN ty-ly

Re: Calculate YOY by ignoring the lowest granularity?

alaynanich — Fri, 24 May 2024 22:25:59 GMT

can't seem to upload files so here's a copy of the sample data
PeriodMonthstore idtranssku #sales_typesalesunitsgpSales YOY

LY	FEB	18175	181752023-02-24 00:00:005511549364037	1	online	6.99	1	1.56	-6.99
LY	FEB	11577	115772023-02-24 00:00:005751125188256	2	online	2	2	0.04	-2
LY	FEB	11577	115772023-02-24 00:00:005761111395438	3	online	1	1	0.02	-1
TY	FEB	14296	142962024-02-24 00:00:0046015415216371	4	online	19.99	1	7.08	19.99
LY	FEB	14296	142962024-02-24 00:00:002601552543998	4	online	39.98	2	14.15	-39.98
LY	FEB	15483	154832023-02-12 00:00:001121214558430	5	retail	27.99	1	4.7	-27.99
TY	FEB	17448	174482024-02-26 00:00:00587111326649	6	retail	6.49	1	1.54	6.49
LY	FEB	17749	177492024-02-08 00:00:001551436101558	7	retail	8.99	1	3.04	-8.99
TY	FEB	17749	177492024-02-08 00:00:001531309451558	7	retail	8.99	1	3.04	8.99
TY	FEB	17353	173532024-02-23 00:00:00204708332516	8	retail	25.16	4	9.49	25.16

Re: Calculate YOY by ignoring the lowest granularity?

xifeng_L — Sat, 25 May 2024 04:13:36 GMT

Hi alaynanich ,

You can refer to below column expression.

But I think it would be more accurate to use a measure to calculate YOY instead of calculated column, since the calculated column is static.

Sales YOY (Measure) = var ty = CALCULATE(SUM('Table'[sales]),'Table'[Period]="TY") var ly = CALCULATE(SUM('Table'[sales]),'Table'[Period]="LY") RETURN ty-ly

Demo - Calculate YOY by ignoring the lowest granularity.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Calculate YOY by ignoring the lowest granularity?

alaynanich — Mon, 27 May 2024 15:01:21 GMT

Thank you! I think there was osmething wrong with my data model so now my original calculation is working, but appreciate your response!! Noted about the columns vs measures