Forum Discussion
calculate Monthly average based on YTD value
deevs when working with dates, it is a best practice to add a calendar dimension in your model and you can easily create one following my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
Once you have calendar dimension, create a relationship with the transaction table, it will be one to many relationship.
Add the following two measures
Avg Numerator =
VAR __current = MIN ( CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ), VALUES ( 'Calendar'[Year] ) ), TODAY() )
VAR __startOfMonth = EOMONTH ( __current, -1 ) + 1
VAR __endOfMonth = EOMONTH ( __current, 0 )
VAR __daysUntilToday = CALCULATE ( COUNTROWS ( 'Calendar' ), DATESBETWEEN ( 'Calendar'[Date], __startOfMonth, __current ) )
VAR __dayUntilEOM = CALCULATE ( COUNTROWS ( 'Calendar' ), DATESBETWEEN ( 'Calendar'[Date], __startOfMonth, __endOfMonth ) )
VAR __avgNumerator = MONTH ( __current ) - 1 + ( DIVIDE ( __daysUntilToday, __dayUntilEOM ) )
RETURN __avgNumerator
Average =
VAR __actual = CALCULATE ( SUM ( 'Table'[Actual] ), ALLSELECTED ( 'Calendar' ), VALUES ( 'Calendar'[Year] ) )
RETURN
DIVIDE ( __actual, [Avg Numerator] )
To visualize the data, use the month column from the Calendar table and above measures, and that will do it.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡