How to calculate Average Monthly Revenue

[emma313823]

Hi All,

I am working on a report build where the owners want to see card visuals showing Average Monthly Revenue for the last two full years and the current year. I created a measure which I beleieve is working for the full year of 2022 and 2023. I believe this is saying sum the Revenue (which I have restricted to each year on the card visual, then divide by 12 months). 

AvgMoRev = DIVIDE(SUM('Acctg Revenue'[Revenue]),

    COUNT('Acctg Revenue'[Month]))

I need to do this for 2024, but I'm stuck. Is there a way to modify the formula, so it will automatically sum up what I have for 2024 and then divide by the number of months completed? For example...Revenue for 2024 is in through June and we are still in July receiving revenue...so I only want the formula to calculate and provide the average revenue based on the previous month ended. Is this possible?

Emma

[PhilipTreacy · Accepted solution]

Hi @emma313823 

Download example PBIX file

You can use this to calculate your Avg Monthly Revenue up to the end of the previous month in the current year

AvgMoRevCurrYear = DIVIDE(CALCULATE(SUM('DataTable'[Value]), FILTER('DataTable', 'DataTable'[Date] < EOMONTH(TODAY(),-1))) , (MONTH(TODAY())-1), 0)

Regards

Phil