Average Total of selected Month and 12 months before

[apanta]

Hi Power BI Gurus,

I've been stuck for a few days figuring out the DAX to calculate the average value of the selected month and 12 months before. For example, with the below data, what I want is when the month is selected as December 2023 in a slicer, I want to get the average from Jan 2023 to December 2023 and keep that new average as a constant value for all those months in a new column. Similarly, for Jan 2024, the average should be calculated from Feb 2023 to Jan 2024 and keep that value as a new constant value from Feb 2023-Jan 2024 as shown in the below example.

Any guidance would be greatly appreciated.

Thank you!

[Element115]

Not exactly the same, but here is how I implemented a 12-month rolling average:

AD_COUNT_12MOS_ROLLING = 
VAR __month_in_period       = 12
VAR __last_date             = MAX( 'DimDate'[Date] ) 
VAR __lookback_period       = DATESINPERIOD( 'DimDate'[Date], __last_date, - __month_in_period, MONTH )
VAR __date_with_data        = MAX( ADData[Date] ) 
VAR __lookback_filter       = TREATAS( { "Weekday", "Weekend" }, 'DimDate'[DayType] )
// count for each 12 mos period starting from end of month prior to current month
VAR A = 
    CALCULATE( 
        [AD_COUNT],
        __lookback_period,
        __lookback_filter
    )

RETURN

IF ( 
    COUNTROWS( __lookback_period ) >= 365               && 
    __date_with_data <= LASTDATE( __lookback_period )   && 
    LASTDATE( __lookback_period ) <= EOMONTH( MAX( 'Last Refresh DateTime (PQ)'[DateTime]), -1 ), // this skips the current or last month available in DimDate
    
    IF ( ISBLANK( A ), 
        0,
        A
    )
)

and the computed table 'Last Refresh DateTime (PQ)' (done in M, not in DAX, that's why PQ in the name, ie Power Query) is done like so:

let
    Source = DateTime.LocalNow(),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "DateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"DateTime", type datetime}})
in
    #"Changed Type"

So that the data displayed in the table/matrix viz remains in sync with the dates for which there is data, because if for 1 month there was no refresh, you don't want these extra 4 weeks of blank data to mess up things.  Also, the code always grabs data from the previous month for the whole month, so not the current month, as the requirement was always to show complete months.

[AD_COUNT] is a simple measure:

AD_COUNT = COUNT( ADData[AcSe] )

Anyway, that's the gist of it. Hope this helps. (Please thumbs up and accepted solution if you find it helpful.)

[Element115]

Kinda like a moving avg window over a period length of 12 months?  I forget the technical term now, but I remember I had to solve something very similar 2 years ago.  I'll look at my code tomorrow and let you know.

[apanta]

Thank you so much! Will wait for your reply.

[Element115]

So...  did that help?