YTD Average of Measure

Question

Can anyone help me in creating a YTD Average % Calculation?

I have created 'A','B' by using the following DAX

A = DIVIDE([Current Month W/Allowance Over 90 $],[Aging],0)

B = DIVIDE([UnderBill],[OverBill],0)

Now I need to create a YTD Average % calculation based on the above two calculations.

This is what I am looking for

YTD A = Average of 'A' ( This average should be YTD)

YTD B = Average of 'B' ( This average should be YTD)

enter image description here

So if we look at YTD A for 08/01/2019, in excel I did the Average =AVERAGE(C2:C9) and the result is 65% and for next month it should be =AVERAGE(C2:C10) and the result is 61%

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-11-08T04:57:48

Assuming there is a Calendar table which has Calendar[Year] and Calendar[YearMonth] columns, here is a possible solution.

YTD A = 

-- Calculated table which has start and end dates of each YTD months
VAR YearMonthsYTD =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER( 
            ALL( 'Calendar' ),
            'Calendar'[Year] = MAX( 'Calendar'[Year] )
                && 'Calendar'[Date] <= MAX( 'Calendar'[Date] )
        ),
        'Calendar'[YearMonth]
    ),
    "@StartDate", CALCULATE( MIN( 'Calendar'[Date] ) ),
    "@EndDate", CALCULATE( MAX( [Date] ) )
)

-- Calculate the value for each month, and get the average of them
RETURN
AVERAGEX(
    YearMonthsYTD,
    CALCULATE(
        [A],
        FILTER(
            ALL( 'Calendar' ),
            'Calendar'[Date] >= [@StartDate] && 'Calendar'[Date] <= [@EndDate]
        )
    )
)

BTW, although I'm ignorant in accounting, I'm doubting if your calculation logic is correct, because average of percentages will not be an appropriate measure in general.

In the presented scenario, wouldn't this definition be more appropriate?

YTD A = DIVIDE( <YTD value of numerator>, <YTD value of denominator> )

This is not only to make it correct, but also to make it much easier.

YTD A = CALCULATE( [A], DATESYTD( 'Calendar'[Date] ) )

Forgive and forget if this is not a relevant comment for the specific case of OP.

YTD Average of Measure

1 Answers