Forum Discussion
YoY Qty Measure
tenfingers Anonymous amitchandak
Thanks Guys for your help. I found a very simple soloution from my problem.
i mades some new basic measures.
1. i summarized all the sales =SUM(TotalSales[Qty])
2. Qty Last Year = CALCULATE(TotalSales[Qty], SAMEPERIODLASTYEAR (Date[Date]))
3. Differnece from This year to Last year = IF( ISBLANK ([QtyLY]) , BLANK(), [TotalSales]-[QtyLY] )
result fits perfect for me.
Thx again
regards Tom
If you would like to calculate the difference between two columns in your underlying table, you can use SUMX:
SUMX(
Table,
'Table'[Column 1] - 'Table'[Column2]
)
SUMX goes line by line through the table (iterates) and minuses column 2 from column 1 for each row then sums the amount. For each row in the visual the sum is just the result of the current row but for a row total at the bottom of the visual , it is the sum of all the differences from each row.
If you have years as a column and have a measure as your calculation then you can use the following:
CALCULATE ( [Measure], Date[Year] = 2020) -
CALCULATE ( [Measure], Date[Year] =2019)
Thx for your quick response but i don´t get it☹️
i tried to create a table with 3 columns (PartNumber, 2020, 2019) to apply your code but i can only create a table where PartNumber is first column and 2020 and 2019 are in the same column below each other and to next to them. what i´m doing wrong?
Sorry I probably didn't explain very well and you can disregard the Sumx part of my post as it's not relevant to your requirements. No need to create a new table. To calculate the difference between the 2019 qty and the 2020 qty you just need to create new measure that minuses one from the other. The measure should be in this format:
CALCULATE( SUM( 'FactTable'[Qty column]), 'DateTable'[Year] = 2020) -
CALCULATE ( SUM( FactTable[Qty column]), 'DateTable'[Year] =2019)
where FactTable is the name of your fact table and DareTable is the name of your date table assuming that you have a model setup in a star schema
tenfingers Anonymous amitchandak
Thanks Guys for your help. I found a very simple soloution from my problem.
i mades some new basic measures.
1. i summarized all the sales =SUM(TotalSales[Qty])
2. Qty Last Year = CALCULATE(TotalSales[Qty], SAMEPERIODLASTYEAR (Date[Date]))
3. Differnece from This year to Last year = IF( ISBLANK ([QtyLY]) , BLANK(), [TotalSales]-[QtyLY] )
result fits perfect for me.
Thx again
regards Tom
HI Thomas_Ramhapp,
Glad to hear you find and share the solution here, I think it should be helpful if someone else has faced a similar issue and try to find out the suggestions on power bi community.😊
Regards,
Xiaoxin Sheng