Forum Discussion
Average Per Month Issues
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:
*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.
Many thanks in advance.
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
6 Replies
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
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?
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 youHi 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 youHi @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
