Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
globe11123
Helper I
Helper I

Monthly Turnover Average YTD

I'm trying to work out the monthly turnover for 22/23, we want the calculation to only take into account full months.

 

For example average for this month would be April+May / 2 then next month it would be April+May+June / 3 etc.

 

MonthlyAvg22-23 = (CALCULATE(SUM(INVOICE[NetTurnoverGBP]),('Calendar'[Fiscal Year])="22-23", filter 2)))
 
I've tried by using the above calculation but it becomes tricky when your trying to compare the month you are on against the fiscal month number. Any suggestions?
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @globe11123 ,

 

You could create a column to check if the month is a full month.

For example:

isfullmonth = 
IF(ENDOFMONTH('Table'[date]) = DATE(YEAR('Table'[date]),MONTH('Table'[date])+1,1)-1,1,0)

 Then add this column as a filter condition to your formula.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @globe11123 ,

 

You could create a column to check if the month is a full month.

For example:

isfullmonth = 
IF(ENDOFMONTH('Table'[date]) = DATE(YEAR('Table'[date]),MONTH('Table'[date])+1,1)-1,1,0)

 Then add this column as a filter condition to your formula.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@globe11123 , to get monthly avg of measure, You can try a measure like

 

AvergaeX(Values('Date'[Month Year]), [Measure])

 

Prefer to use date table, else use month year from your table

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.