Measuring Average Delta

[nsadams87xx]

Hey everyone,

I'm having an unusual issue with calculating an average difference.  Basically, my goal is to calculate the 2 month average difference of the current year and last year of the length of stay in minutes patients stayed in the emergency room.  So my arithmatic would be:

((Average of Jan and Feb 2019) - (Average of Jan and Feb 2018))/(Average of Jan and Feb 2018)

My Power BI is as follows:

LOS Mean 2 Mo % Change = (AVERAGE('2 Month Timing Data'[los_mm]) - CALCULATE(AVERAGE('2 Month Timing Data'[los_mm]),SAMEPERIODLASTYEAR(Dates[the_date])))/CALCULATE(AVERAGE('2 Month Timing Data'[los_mm]),SAMEPERIODLASTYEAR(Dates[the_date]))

This is all fine but when I do the actual QA'ing of my formulas, I'm finding that Power BI is a touch off.  Power BI is only off by about .5% low but in this case where there are 60 or so dates to measure and anywhere between 5 to over 500 minutes per stay, my user says this is not acceptable.  I'm inclined to believe that too because my average for 2018 was 179 and for 2019 it was 182.  So with these potentially small differences, I can understand why we want very particular accuracy.

My "los_mm" column is formatted as a Decimal Number and its accuracy is to the hundreth.  The data in my data mart is all whole numbers.

Any suggestions?  I'm sorry, I cannot share my pbix file or a data sample due to the data being ultra sensitive/healthcare related.

[nsadams87xx · Accepted solution]

Additional notes:

So (182 - 179)/179 = 1.68%

Power BI is calculating 1.29%.  So .5% off was a little aggressive, my apologies.

[nsadams87xx]

Issue resolved, there isn't actually an issue either.  I'm presenting my raw column as an "Average of LOS" and my calcuation is more accurate.  Power BI is doing exactly what it's told and accurately but within two different contexts in which my report is not accouting for in the presentation.  So both are right at the same time.  My user will have to decide how the report will be presented in a way that will make sense.

[v-juanli-msft]

Best Regards
Maggie