Forum Discussion

deevs's avatar
deevs
Icon for Helper III rankHelper III
5 years ago

calculate Monthly average based on YTD value

Hello ,  I am trying to create a measure to apply  on the below table as a column . I want to calculate YTD  monthly average  by dividing total cost by number of months completed but the current mo...
Need Help
Show and Tell
Tips and Tricks
parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

deevs something is missing here, share the measures you added to the model, make sure you added these as measures, not columns. also share the relationship image between the calendar table and the transaction table.

deevs's avatar
deevs
Icon for Helper III rankHelper III
5 years ago
Avg Numerator =
VAR __current = MIN ( CALCULATE ( MAX ( 'DATE'[Date]), ALLSELECTED ( 'DATE' ), VALUES ( 'DATE'[Year]) ), TODAY() )
VAR __startOfMonth = EOMONTH ( __current, -1 ) + 1
VAR __endOfMonth = EOMONTH ( __current, 0 )
VAR __daysUntilToday = CALCULATE ( COUNTROWS ( 'DATE' ), DATESBETWEEN ('DATE'[Date], __startOfMonth, __current ) )
VAR __dayUntilEOM = CALCULATE ( COUNTROWS ( 'DATE' ), DATESBETWEEN ( 'DATE'[Date], __startOfMonth, __endOfMonth ) )
VAR __avgNumerator = MONTH ( __current ) - 1 + ( DIVIDE ( __daysUntilToday, __dayUntilEOM ) )
RETURN __avgNumerator
 
 
Average =
VAR __actual = CALCULATE ( [Actual], ALLSELECTED ( 'DATE' ), VALUES ( 'DATE'[Year] ) )
RETURN
DIVIDE ( __actual, [Avg Numerator] )