calculate Monthly average based on YTD value

[deevs]

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 month number should be based on the current day .
For an example as of sept 14 ,
Total number of months should be = 8+ (14/30) = 8+0.4666=8.47
then  divide the YTD value by that amount to get the monthly average.
any help is greatly appreciated.

[parry2k]

@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]

@parry2k 

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] )

[parry2k]

@deevs you are not answering my question. My question is from which table you are using Month in your visuals. If you please read my question and reply, it will help otherwise there is no point going back and forth.

[deevs]

@parry2k  im using calendar table

[parry2k]

@deevs from which table you are using month in your visual?

[deevs]

@parry2kcalendar table

[parry2k]

@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 Solutio...

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.⚡

[deevs]

@parry2k  It is truly appreciable that the result was computed appropriately as a total. Could you also kindly explain how I get the total for each row across months?

[deevs]

@parry2k 

what i eventually want is to convert that table to a chart  so the YTD monthly average appears as a straight horizontal line.

[parry2k]

@deevs why do you want a column for avg? why this cannot be a measure?

[deevs]

@parry2k 

Sorry for any confusion...what I meant by column was to create a measure and add to the table visual. I updated the screenshot to reflect that