AverageEx fails when a datapoint does not exist. It's being short circuted.

[JWolmanSymplr]

AverageEx fails when a datapoint does not exist. It's being short circuted.

We are trying to get an average calculation of days for a rolling 180 day period but we are finding that if we have a month that has not information Power BI is short circuting the calculation and I cannot just try to figure out how to solve this.

For example the data:

Item 1: August 2024 - Days 12

Item 2: August 2024 - Days 5

Item 3: August 2024 - Days 9

Item 4: Sept 2024 - Days 47

Item 5: Sept 2024 - Days 8

Item 6: Oct 2024 - Days 12

Item 7: Dec 2024 - Days 4

Item 8: Jan 2025 - Days 86

Item 9: Jan 2025 - Days 13

Rolling Days by Month are as follows:

August = 8.67 (which I expect)

Sept = 16.20 (which I epxect)

Oct = 15.50 (which I expect)

Nov = 0 (which is wrong - because there are no 'Items' in November this is getting short circuted.

Dec = 13.86 (which I expect)

Measure we are using:

Rolling Average Time to Close =

CALCULATE(AVERAGEX(

   filter( allselected('Items'),

           'Items'[Closed Date] <= max('Items'[Closed Date] )

        && 'Items'[Closed Date] >= max('Items'[Closed Date] ) - 180),

       'Items'[Actual Start To Close]), USERELATIONSHIP('Relations Calendar'[Date],'Items'[Closed Date])) + 0

Because November has no data Power BI is short circuting the AverageEx and it should not be, it should still get the previous months data

How do I get November to be what I would expect which should be the 15.50 (same for October)?

[MasonMA · Accepted solution]

Hi,

Try this so that your measure uses Calendar dates for anchors and iterator comes from the Calendar table.

Rolling Average Time to Close =
VAR _maxDate = MAX('Relations Calendar'[Date])
VAR _minDate = _maxDate - 180
RETURN
CALCULATE (
AVERAGEX (
FILTER (
ALL ( 'Items' ),
'Items'[Closed Date] >= _minDate &&
'Items'[Closed Date] <= _maxDate
),
'Items'[Actual Start To Close]
),
USERELATIONSHIP ( 'Relations Calendar'[Date], 'Items'[Closed Date] )
)

[JWolmanSymplr]

I think I need to change the ALL to ALLSELECTED like I had in my original post. I am checking it but I think it might be working!

[MasonMA]

Yes, if you want to preserve page/visual slicer selections. This way you keep the external selection context. 

[JWolmanSymplr]

That returns too high of values because I believe _maxDate is being set wrong.

The values I am getting now are as follows:

August = 40.07 (this is wrong)

Sept = 42.29 (this is wrong)

Oct = 45.41 (this is wrong)

Nov = 43.13 (this is wrong - but I am getting a number)

Dec = 44.50 (this is wrong)

[djurecicK2]

Deleted

[MasonMA]

I'm guessing this 'Relations Calendar' table would be user's date/calendar table. 🙂 but maybe I'm wrong. 

[djurecicK2]

Yeah, you are right 🙂 Have some Kudos.

[djurecicK2]

Deleted