variance between values in two different columns

[ericbuildOn]

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.

[Greg_Deckler]

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.

[ericbuildOn]

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

[v-danhe-msft · 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:

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