Calculate YOY by ignoring the lowest granularity?

[alaynanich]

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

[xifeng_L · Accepted solution]

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~

[alaynanich]

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

[alaynanich]

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