Average based on the number of months that have revenue

[Yuiitsu]

Hi All

I need help on this result that I wish to get

Example: 

I want to get the average revenue of customer X based on the last 6 months. (Or any amount of months I choose in the slicer)

So in the slicer I choose the last 6 months (May till Oct) , however customer X does not have revenue every month.

Based on the screenshot below, X only have revenue on June and July. So I need the formula to work this way:

362,640 / 2 = 181,320

Similarly if i choose another customer Y now that only have sales for 4 months.

The formuala should be:

680,254 / 4 = 170,064

Tried using the average function but the result is not accurate.

Any idea how to write this DAX?

(I tried to 1st create a measure to count month if revenue is not blank but i think it is not working. The result is always the number of month i choose in the slicer and not how many months have revenue)

MonthCount = 
    CALCULATE(
        DISTINCTCOUNT('Date'[Month Name]),
            NOT(
                ISBLANK('Master Sales'[Revenue]
                
            )
        ))

[v-cgao-msft · Accepted solution]

Hi @Yuiitsu ,

I noticed in the posted image that [Revenue] is not aggregated, at this point it passes a screener up filter to [Average of Revenue ], please try changing [Revenue] to [Sum of Revenue], at this point [Average] should work.

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group