Getting the average value from YTD dax value for the Last 12 months

[james_b]

Hi all,

I am trying to calculate the rolling 12m average derived from monthly YTD Dax measure value; I have so far miserably failed to calculate this so far!

For instance, with simple data in excel. With L12m AVG as the target

The YTD DAX measure is a simple calculate dates YTD with date table...

This must be achievable in DAX (I do not want to make a running total column in Power Query as v slow), as the graph can calculate the average line!

Any help greatly appreciated!
James

[v-jingzhang]

Hi @james_b 

You could try this measure

L12M Avg = 
AVERAGEX (
    CALCULATETABLE (
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
    ),
    [YTD DAX]
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[james_b]

Hi,

Unfortunately the solution is not correct as per the BI Model. See image below

Thanks,

James