Get the Average sum of previous 3 months in PowerQuery

[cgs13]

Hello,

I have a table with several values. 3 of those are:

• Month (1st day of the month)
• TeamName
• Calls

I need to get the average sum of the previous 3 months for each team. I tried with ChatGPT but it gave me a formula that takes minutes to complete just 1 row and calculates some stuff that it doesnt use in the formula at all so I guess it's really inefficient.

Is there a clear cut way of doing this?

I'm not familiar with M but I was thinking something like a combination of SUM, each and DATE.AddMonth should work?

[cgs13]

Would that let me get the sum for all months or just current? because I need this report to calculate that average for all months available in the data. E.g. for June it should use March, April, May. For May then Feb, March, April, etc.

[Anonymous]

Hi,

Thanks for the solution @peterpan  provided, and i want to offer some more information for user to refer to.

hello @cgs13 , you can create a custom column e.g 

let a=[Month],
b=Date.AddMonths(a,-3),
c=[TeamName]
in List.Average(Table.SelectRows(#"Changed Type"(replace it with your last step name), each [Month]>=b and [Month]<a and [TeamName]=c)[Calls])

Output

And you can refer to the following m code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc+7CcAwDEXRXVQbYsn/WYyLZP8hkjjh8VCh5iAk7pxi0fKhh0qQ8xmNssKvBjXSBE2kGZpJC7SQVmglbdBG2qGddEAH6VdxvRXqK7aar9jafcXWWnzG5lJ9h7/RoJbUh/jlQQ9lrRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, TeamName = _t, Calls = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"TeamName", type text}, {"Calls", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Average", each let a=[Month],
b=Date.AddMonths(a,-3),
c=[TeamName]
in List.Average(Table.SelectRows(#"Changed Type", each [Month]>=b and [Month]<a and [TeamName]=c)[Calls]))
in
    #"Added Custom"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cgs13]

This seems to be the answer, however as another answer said, it seems like M Code is too slow to load it. Everything in the formula seems to be trying to do exactly what I want and your result does that. I ran it and it took almost a minute to run one row and I have more than 100k rows (used to have 320k but already trimmed it to see if it works).

I tried the feedback of maybe using DAX so I added the table to the data model and used ChatGPT to convert the formula to DAX for PowerPivot and it gave me this:

AverageCalls =
CALCULATE(
AVERAGE(TableName[Calls]),
FILTER(
ALL(TableName),
TableName[Month] >= EDATE(MAX(TableName[Month]), -3) &&
TableName[Month] < MAX(TableName[Month]) &&
TableName[TeamName] = MAX(TableName[TeamName])
)
)

This also seems to try to do what I want but I'm getting blank as a result, both if I add it as a column or if I add it as a Measure below. Do you know what may be causing this? I just need this resolved to finish a project I've been working on for months so I'd appreciate any help.

Edit: I found why it was blank as I was using a column from another table in the filters, however now that I fixed that, its giving me the same value for all rows. I'm not sure if that MAX is the right function to get the current row TeamName.

[Anonymous]

Hi @cgs13 

You can try the following measure.

MEASURE =
VAR a =
    MAX ( 'Table'[Month] )
VAR b =
    EDATE ( a, -3 )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Month] >= b
                && [Month] < a
                && [TeamName] IN VALUES ( 'Table'[TeamName] )
        ),
        [Calls]
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cgs13]

I added it as a column in the table in PowerPivot but it's giving me the same value for all rows:
Below the code with the actual column names just in case you can spot any mistake I might be doing:

NewAverageCalls:=
VAR a =
MAX (ForecastedCalls[YearMonth]
VAR b =
EDATE (a, -3)

RETURN
AVERAGEX(
FILTER(
ALLSELECTED( ForecastedCalls ),
ForecastedCalls[YearMonth] >= b
&& ForecastedCalls[YearMonth] < a
&& ForecastedCals[BMT.Product Line Description] IN VALUES ( ForecastedCalls[BMT.Product Line Description]) 
),
ForecastedCalls[[FinalCalls___]]]
)

//I tried using both 'ForecastedCals[BMT.Product Line Description]' and '[BMT.Product Line Description]' and both gave me the same result

[Anonymous · Accepted solution]

Hi @cgs13 ,

The solution i offered is a measure instead of a calculated column, if you want to use it as a calculated column, you need to change it to the following.

NewAverageCalls :=
VAR a = ForecastedCalls[YearMonth]
VAR b =
    EDATE ( a, -3 )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( ForecastedCalls ),
            ForecastedCalls[YearMonth] >= b
                && ForecastedCalls[YearMonth] < a
                && ForecastedCals[BMT.Product Line Description]
                    = EARLIER ( ForecastedCalls[BMT.Product Line Description] )
        ),
        ForecastedCalls[[FinalCalls___]]]
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[peterpan]

You can use DATEADD like this-

Last3MonthCallMade = CALCULATE(SUMX(Table1,Table1[Calls Made]),DATEADD(Table1[Date],-3,MONTH))

I have attached a google drive link with the working solution for your reference. It entails more than 1.4 lakh rows. Let me know if you any further assistance.

Google Drive Link 

[peterpan]

Unfortunately in power query this will be a time consuimg process. I suggest you to use DAX function like calculate and DateAdd since it would be simpler there. Let me know if that suits your requirement.

[peterpan]

First put a filter on date column with Date.AddMonth and select -3 in month. Now you can Groupby based on team and in aggregation, select Calls column and aggregate by Average.