Need a 3 month rolling average but the first value should stay the same

[PowerUser2000]

Hello,

I am trying to create a 3 month rolling average calculation for my power bi report.

Example:

Product 1       Value          Date
Dog                 100            2024-05
Cat                  200             2024-06
Wolf                300            2024-07

I want the output to be with moving average for dog to be 100, then cat should be 200 + 100 /2 and then wolf should be 300+200+100 then divided by 3. I feel this is the best way to showcase your data because I don't know how to keep the same contect for first and second value without skewing the data?

this is what I have so far but it just returns the normal count of accounts by date and product no averaging for some reason is taking place:

3M Avg Direct Deposit Accounts by Product =
VAR SelectedMonth = SELECTEDVALUE(VW_DIRECT_DEPOSIT_V10[REPORT_DATE])
VAR CurrentProduct = SELECTEDVALUE(VW_DIRECT_DEPOSIT_V10[PRODUCT])

-- Get the past 3 months including the current one
VAR RollingDates =
FILTER (
VALUES(VW_DIRECT_DEPOSIT_V10[REPORT_DATE]),
VW_DIRECT_DEPOSIT_V10[REPORT_DATE] <= SelectedMonth &&
VW_DIRECT_DEPOSIT_V10[REPORT_DATE] >= EOMONTH(SelectedMonth, -2)
)

-- Create a table of distinct month-level counts for the product
VAR MonthCounts =
ADDCOLUMNS (
RollingDates,
"MonthlyDDAccounts",
CALCULATE (
DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),
VW_DIRECT_DEPOSIT_V10[DIRECT_DEPOSIT_YN] = "Yes",
VW_DIRECT_DEPOSIT_V10[PRODUCT] = CurrentProduct,
VW_DIRECT_DEPOSIT_V10[REPORT_DATE] = EARLIER(VW_DIRECT_DEPOSIT_V10[REPORT_DATE])
)
)

-- Remove months with blank values
VAR ValidMonthCounts =
FILTER(MonthCounts, NOT(ISBLANK([MonthlyDDAccounts])))

VAR TotalAccounts = SUMX(ValidMonthCounts, [MonthlyDDAccounts])
VAR NumMonths = COUNTROWS(ValidMonthCounts)

RETURN
IF(NumMonths > 0, DIVIDE(TotalAccounts, NumMonths), BLANK())

[v-mdharahman]

Hi @PowerUser2000,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to calculate rolling average for 3 months of data with a fixed first value. As @pankajnamekar25 has already responded to your query, kindly go through his response and check if your issue can be resolved.

I would also take a moment to thank @pankajnamekar25, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[pankajnamekar25]

Hello @PowerUser2000 

Try this measure

3M Avg Direct Deposit Accounts by Product =

VAR SelectedMonth = SELECTEDVALUE(VW_DIRECT_DEPOSIT_V10[REPORT_DATE])

VAR CurrentProduct = SELECTEDVALUE(VW_DIRECT_DEPOSIT_V10[PRODUCT])

-- Get all prior months (including current)

VAR AllPriorMonths =

    FILTER (

        VALUES(VW_DIRECT_DEPOSIT_V10[REPORT_DATE]),

        VW_DIRECT_DEPOSIT_V10[REPORT_DATE] <= SelectedMonth

    )

-- Sort and pick last 3

VAR Last3Months =

    TOPN (

        3,

        AllPriorMonths,

        VW_DIRECT_DEPOSIT_V10[REPORT_DATE], DESC

    )

-- Create list of values for the last 3 months

VAR MonthCounts =

    ADDCOLUMNS (

        Last3Months,

        "MonthlyDDAccounts",

            CALCULATE (

                DISTINCTCOUNT(VW_DIRECT_DEPOSIT_V10[ACCTNBR]),

                VW_DIRECT_DEPOSIT_V10[DIRECT_DEPOSIT_YN] = "Yes",

                VW_DIRECT_DEPOSIT_V10[PRODUCT] = CurrentProduct,

                VW_DIRECT_DEPOSIT_V10[REPORT_DATE] = EARLIER(VW_DIRECT_DEPOSIT_V10[REPORT_DATE])

            )

    )

-- Filter valid (non-blank) months

VAR ValidMonthCounts = FILTER ( MonthCounts, NOT ISBLANK ( [MonthlyDDAccounts] ) )

-- Sum and average logic

VAR TotalAccounts = SUMX ( ValidMonthCounts, [MonthlyDDAccounts] )

VAR NumMonths = COUNTROWS ( ValidMonthCounts )

RETURN

DIVIDE ( TotalAccounts, NumMonths )

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.