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
ericbuildOn
New Member

variance between values in two different columns

I have data being summarized in service hour field, and then have that segmented by two separate text categories (Month) and (School Year). I would like to know the variance in the summarized Service Hour field between the same month but previous school year. Example shown below.

 

Screenshot (1)_LI.jpg

1 ACCEPTED SOLUTION

Hi @ericbuildOn,

Based on my test, you could refer to this formula:

Variance = var a=CALCULATE(MAX('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year])))
var b=CALCULATE(MIN('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year])))
return a-b

Result:

1.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/6wx2m7jkn6gwgs8/variance%20between%20values%20in%20two%20different%20colum...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

Sample/example data that can be copied and pasted would be tremendously helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

That being said, you should be able to create a couple VAR's that grab your MAX month and MAX year and then another VAR that does a SUMMARIZE of ALL and then some additional VAR's that use your month and year variables to extract the particular information that you want through the appropriate FILTERing. Then you just need to RETURN the calculation that you want.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thanks for the tip and for that detailed response Greg. I'll go ahead and try that out. In the meantime, here is a sample of the data:

 

RegionHigh SchoolService HoursService Goal% Service TargetService DateSchool Year
BostonHigh School Name22.759752.33%1-Sep'16/'17
BostonHigh School Name72.59757.44%1-Sep'17/'18
BostonHigh School Name116.597511.95%2-Oct'16/'17
BostonHigh School Name223.597522.92%2-Oct'17/'18

Hi @ericbuildOn,

Based on my test, you could refer to this formula:

Variance = var a=CALCULATE(MAX('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year])))
var b=CALCULATE(MIN('Table1'[Service Hours]),FILTER(ALL('Table1'),'Table1'[School Year]=MAX('Table1'[School Year])))
return a-b

Result:

1.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/6wx2m7jkn6gwgs8/variance%20between%20values%20in%20two%20different%20colum...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors