Forum Discussion
Monthly Average that Changes Based on Date Selection
Hi,
This measure pattern should work
Average monthly sales = AVERAGEX(VALUES(ddate[Month name]),[Total sales])
I have assumed that Total sales is a measure that you have written. To your slicer, drag Month name from the ddate table.
Thank you for your responses, but after further checks it seems this average is not applying correctly when there is no underlying sales for products within at least some of the selected date range. For instance, I have certain products with sales in only 10 months of 2023. When I apply my date range for the whole of 2023, the overall sales for theses products should still be divided by 12 but instead only seem to be dividing by 10. What needs to be adjusted it seems is to superimpose the number of months I am filtering to define the monthly average (i.e. the denominator), regardless of whether there are sales for products in said months within my fSales table.
Please note that sometimes there are simply no entries (or rows) of data to be found whatsover within such months for the products, but it could also be the case that there is data but just no sales are listed against it (i.e. the Sales column is blank).
Do you have any ideas how I can acheive this? Thanks
- Ashish_Mathur
Super User
Hi,
Share some data to work with and show the expected result under various scenarios (especially the one that you have mentioend in your last paragraph).