How can I calculate a monthly average including months with no records in Power BI

1
votes

I'm trying to calculate a monthly average number of cases for each investigator. It might be over a quarter, year, or multiple years so it needs to respond to the visual or table context I drop it into. The base table has Case (individual case#), Investigator (person name), Date assigned (not shown), and from that date,month and year columns extracted and a YearMonth categorical column.

mockdata

I create a caseCount measure as

caseCount = COUNT('Table'[Case])

enter image description here

I've tried several different ways to calculate the average over all months (in this case 4). Because Mary has cases in each month, her average is correct (1.75) but Sam's uses a denominator = 3, thus doesn't calculate correctly. (returns 1.3 instead of 1). How can I force the calculation to use the full number of months.

Additional notes: There may be cases in the table that fall outside the date range I want so I've tried using a 

Avg = CALCULATE(AVERAGE(caseCount), Table[Date] > #10/31/2019#)

I've also tried several variations using CALCULATE(DIVIDE(), [Date] > 10/31/2019. Everything seems to exclude those months when an investigator had no investigations assigned. I also tried connecting to a Date table and using the Distinct YearMonth value created there.

1
powerbiaveragedax

1 Answers

0
votes

This is because the evaluation context.
I would define the measure as follow: 

VAR _casecount = //count number of cases in the selected period, applied on the fact table
VAR _months = COUNTROWS(CALCULATETABLE(VALUES('Calendar'[Month]), ALLSELECTED('Calendar'))) //count number of months in the selected period
RETURN
_casecount/months

Update

I did not consider the scenario of multi-year periods involving May-2019 and May-2020. Then, let's reframe the solution using DATEDIFF:

VAR _casecount = //count number of cases in the selected period, applied on the fact table
VAR _firstCalendarDate = CALCULATE(MIN('Calendar'[Date], ALLSELECTED('Calendar'))
VAR _lastCalendarDate = CALCULATE(MAX('Calendar'[Date], ALLSELECTED('Calendar'))
VAR _months = DATEDIFF(_firstCalendarDate, _lastCalendarDate, MONTH)
RETURN
_casecount/months