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())

[Anonymous]

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

[Anonymous]

Hi @PowerUser2000,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[Anonymous]

Hi @PowerUser2000,
Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the solution would be awesome for others who might run into the same thing.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Thank you,

Hammad.

[Anonymous]

Hi @PowerUser2000,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this. 

Best Regards,

Hammad.

[pankajnamekar25 · Accepted solution]

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.