Monthly average including no transactions

[admin_xlsior]

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

[AlexisOlson · Accepted solution]

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_xlsior]

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!