Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Werfuks
Frequent Visitor

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:

HC_Avg=DIVIDE(CALCULATE(SUM(Headcount[HC]),ALLEXCEPT(Country_Location,Country_Location[Region],Country_Location[Country],Country_Location[Location],Country_Location[Company])), DISTINCTCOUNT(Month_Year[Month/Year]))

Werfuks_0-1687441536663.png

 

Now I want to calculate the deviation between 2023 to 2022 and tried this simple measure but I failed.

Deviation = CALCULATE(Headcount[HC_Avg], FILTER(Month_Year, [Year=2023)) - CALCULATE(Headcount[HC_Avg], FILTER(Month_Year, [Year=2022))

 

Do you have an idea how I could calculate the deviation between 2022 and 2023?

 

Thank you!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Werfuks , Always use a separate date/year table

 

example

 


//Only year vs Year, not a level below

This 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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
Werfuks
Frequent Visitor

Thank you so much, that's it, I got it!

amitchandak
Super User
Super User

@Werfuks , Always use a separate date/year table

 

example

 


//Only year vs Year, not a level below

This 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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors