Forum Discussion
Monthly average including no transactions
Dear friends,
When I have a transactions that in some case the month is skip means no activity in certain month, and I'm averaging my transaction using this DAX =
avg.monthly sales = AVERAGEX(
VALUES(Dates[Month]),
[net sales]
)
how to get the average factor to be the whole numbers of months ?
The Dates dimension is the usual date table which of course have all complete months, but because my transaction is for example have skip month like :
| Month | Net sales |
| May 2021 | 50 |
| Jun 2021 | 100 |
| August 2021 | 200 |
For monthly average shouldn't be it is 250 / 4 instead 250 /3 ?
Also because the months is deliberately chosen from slicer, so I did choose June 2021, provided it has value or not, I think I want all selected month to be included in the average.
Thanks
Assuming your Dates table doesn't have missing months, you can likely solve this just by adding "+ 0".
avg.monthly sales = AVERAGEX ( VALUES ( Dates[Month] ), [net sales] + 0 )
2 Replies
- AlexisOlsonSuper User
Assuming your Dates table doesn't have missing months, you can likely solve this just by adding "+ 0".
avg.monthly sales = AVERAGEX ( VALUES ( Dates[Month] ), [net sales] + 0 )- admin_xlsiorPost Prodigy
Hmm, haven't thought about that. I actually just change my formula to just use DIVIDE with COUNTROWS(AllSelected(Date[month]))
I will try your approach, it looks like a great idea.
Thanks!