Average Per Month Issues

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