Why does powerbi average/day calculation become inaccurate over longer timespan?

Question

I am trying to create a power BI calculation as an average per day of how many times a code was tripped. The dax calculcation that I have

Count Trips average per Day = 
AVERAGEX(
    KEEPFILTERS(VALUES('ruledata (2)'[Date].[Day])),
    CALCULATE([Count Trips])
)

works fine when the average is being calculated over a couple days. I double checked this by hand and can confirm that it is accurate until at least 2 weeks. However once the time range increases to include months the average starts getting ridiculous and begins displaying average trips /day values that are much higher than the highest number of trips on a single day. I have confirmed that the data in the graph is correct

So I know that the values should reflect what is in the graph. In this two month example the DAX formula calculated the average to be 149.03 but the actual average/day should have been 82.8.

In general is there some sort of error in the DAX formula that I am using that could cause this?

Wouter Wouter · Accepted Answer · 2020-06-21T17:37:17

I guess that 'ruledata (2)'[Date].[Day] is the day of the month. So if you take the average it will be wrong because when you take the average of e.g. March and April you will divide the total trips by 31, and not by 61 (30+31). So use 'ruledata (2)'[Date].[Date] instead.

Why does powerbi average/day calculation become inaccurate over longer timespan?

1 Answers