Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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:
You can also download the PBIX file to have a view.
Regards,
Daniel He
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.
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:
Region | High School | Service Hours | Service Goal | % Service Target | Service Date | School Year |
Boston | High School Name | 22.75 | 975 | 2.33% | 1-Sep | '16/'17 |
Boston | High School Name | 72.5 | 975 | 7.44% | 1-Sep | '17/'18 |
Boston | High School Name | 116.5 | 975 | 11.95% | 2-Oct | '16/'17 |
Boston | High School Name | 223.5 | 975 | 22.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:
You can also download the PBIX file to have a view.
Regards,
Daniel He
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |