Forum Discussion
Calculate the deviation between two columns
Dear Experts, month by month I transfer the workforce into PBI, Now I have a database with a certain amount of single employees. Every data row show an employee with his name, region, country, location, company, headcount, month, year (e.g. Michael Miller, Americas, USA, Denver, ABC Ltd. , 1, 2023, 05). I already calculated an headcount average measure like that:
Now I want to calculate the deviation between 2023 to 2022 and tried this simple measure but I failed.
Do you have an idea how I could calculate the deviation between 2022 and 2023?
Thank you!
Werfuks , Always use a separate date/year table
example
//Only year vs Year, not a level belowThis Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
2 Replies
Werfuks , Always use a separate date/year table
example
//Only year vs Year, not a level belowThis Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAThank you so much, that's it, I got it!
