diff between two Avg YTD Measures

[Anonymous]

Dear Community

I have on my hands two formulas that allegedly represents Avg YTD:

1st:

avgYTDV1_Measure = 

CALCULATE(AVERAGEX(VALUES('Date'[Monthnumber]),AVERAGE(OPS_ParetoTbl[MTBF])),
     FILTER(ALL('Date'),
        'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
    )
)

2nd:

Tot_Measure = AVERAGE(OPS_ParetoTbl[MTBF])
//---------------
AvgX_Measure = AVERAGEX(VALUES('Date'[MonthNameShort]),[Tot_Measure])
//--------------
avgYTDV2_Measure = 
CALCULATE([MTBF_AvgX_Measure],
     FILTER(ALL('Date'),
        'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
    )
)

Result:

Month	avgYTDV2_Measure	avgYTDV1_Measure	Monthly Avg
Jan	10.21	10.21	10.21
Feb	17.97	18.53	25.73
Mar	22.29	22.29	30.94
Apr	24.99	25.16	33.09
May	30.85	29.69	54.25

while [avgYTDV2_Measure] is technically the correct one why is [avgYTDV1_Measure] isn't giving correct numbers??

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can get the answer from the following blog:

Caution When Using Variables in DAX and Power BI

You can also update the formula of measure [avgYTDV1_Measure] as below to get the correct result:

avgYTDV1_Measure =
VAR _avg =
    CALCULATE ( AVERAGE ( OPS_ParetoTbl[MTBF] ) )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Date'[MonthNameShort] ), _avg ),
        FILTER ( ALL ( 'Date' ), 'Date'[Monthnumber] <= MAX ( 'Date'[Monthnumber] ) )
    )

Best Regards

[amitchandak]

@Anonymous , we should always use calculate in x functions  , if we are not using a measure

avgYTDV1_Measure = 

CALCULATE(AVERAGEX(VALUES('Date'[Monthnumber]),calculate(AVERAGE(OPS_ParetoTbl[MTBF]))),
     FILTER(ALL('Date'),
        'Date'[Monthnumber] <= MAX('Date'[Monthnumber])
    )
)

[Anonymous]

@amitchandak Thank you for your prompt reply,

however, while calculate got me closer  to the right answer its still not an exact YTD average

[amitchandak]

@Anonymous , the second one usages monthnameshort , please check if that can make a diff. Also can share what are you getting now with the formula's