Average Per Month Issues

[Creative_tree88]

Hi all - I'm trying to calculate average per month, which I then need to show on a line chart.  I've used the formula:

Average Waits Per Month = AVERAGEX(VALUES('Sample APM Waits'[Stat Date]), [Count Waiting ALL])

*where the formula Count Waiting All = count('Sample APM Waits'[Event Key])

When I put this onto a chart it doesn't work unless I add a column to the data which turns 'Stat Date' into a month format i.e. 2025-01 which is then stored as text in the data.

The problem I have is that I need the chart to be able to show the X axis as Continuous i.e. without every single month showing on the X axis, as the real data I have is 7 years.  I'm overlaying onto other data for a large project and this is the only measure I cannot get to work in a continuous X axis.

The reason I have added the 'Month' column is because the above formula doesn't work unless it's plotted on the chart using this field, rather than the 'Stat Date' field.

I am wondering if there is a better formula to use, which utilises the 'Stat Date' field but still gives me average per month on charts etc.  Am a bit stuck on this one, as the formula essentially works, I just can't plot it effectively on a continuous X axis.

Many thanks in advance.

Sample Data 

[v-dineshya · Accepted solution]

Hi @Creative_tree88 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps for plotting an average number of events per month on a continuous X-axis using the actual Stat Date field:

1. Group your data by month while keeping a date format that Power BI recognizes for continuous axes (e.g. first day of each month as a proper date, not text).

2. Create a measure that averages the monthly counts rather than averaging over daily values.

1. Create a MonthStart column

MonthStart = DATE(YEAR('Sample APM Waits'[Stat Date]), MONTH('Sample APM Waits'[Stat Date]), 1)

Note: This retains the date format and lets Power BI treat it as continuous.

2. Create a measure to count events per month

Monthly Event Count =
CALCULATE(
COUNT('Sample APM Waits'[Event Key]),
ALLEXCEPT('Sample APM Waits', 'Sample APM Waits'[MonthStart])
)

3. Create the average per month measure:

If you want to average the monthly totals across the whole dataset (the mean monthly count over time):

Average Waits Per Month =
AVERAGEX(
VALUES('Sample APM Waits'[MonthStart]),
CALCULATE(COUNT('Sample APM Waits'[Event Key]))
)

4. Plot on a line chart

X-Axis: MonthStart (not text-formatted)

Y-Axis: Average Waits Per Month measure

X-axis Type: Set to Continuous

Note: This way, Power BI can treat the date axis as continuous and will automatically space the months correctly even if some months have no data while showing a smooth trend line.

Please refer the snaps and sample PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Creative_tree88 ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Creative_tree88 ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Creative_tree88 ,

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[samratpbi]

Hi, For a continuous x-axis, you need to have a date column. Please create a date column or transform your existing column into a date format.

If this helps to resolve your problem, then please mark it as solution, thanks - Samrat

[Creative_tree88]

@samratpbi Thanks.  The 'stat date' column is a date column, but I can't use that in the axis when using the measure as stated above.  The measure only works when I use 'Month' in the X axis, in format YYYY-DD which is derived from Stat Date column.

Hence my question to you all.  The measure doesn't show Average Per Month properly if I use the X axis as Stat Date (which is a true date column).

I guess I need another formula to help me - any ideas?