topic Re: Rolling average last 3 months in DAX Commands and Tips

Rolling average last 3 months

jayjay0306 — Tue, 18 Feb 2020 09:13:44 GMT

Hi,
I hope you can help me with a shallange:
I have a Power BI report, where I need to make a measure (I don't have access to the source table) which calculates the rolling average for the last 3 months.
And the rolling average shall be made on the sum per month (not rolling on the date value).
Example:
in the table below, I have the "Daily Sales-Sum per month" in 2019. The datasource is one table (not a datamodel with dimensions and facts), where leaf-level is transactions by date.
I need the rolling average on the monthly sum-values.

I have tried to make the measure (please see below), but the result is wrong(table above).

DAX-Script:

--------------------------------------
Rolling Average 3 months =
VAR LastDate_ = LASTDATE(Table[Calendar Day])
RETURN
AVERAGEX(
DATESINPERIOD(
'Table'[Calendar Day];
LastDate_; -3; MONTH);
SUMX(
KEEPFILTERS(VALUES('Table'[Month]));
CALCULATE(SUM('Table'[Sales]))
)
)
---------------------------------------------

the result I need is this (here shown for the last two months, as an example):

Any ideas? All inputs will be greatly appreciated.

Thanks.

Br,

JayJay

Re: Rolling average last 3 months

Greg_Deckler — Tue, 18 Feb 2020 11:23:17 GMT

I wrote this, Rolling Months Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499#M124

Also, if that doesn't work, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Rolling average last 3 months

Anonymous — Wed, 19 Feb 2020 08:59:45 GMT

How about this?

Rolling Average 3 months = VAR LastDate_ = LASTDATE ( Table[Calendar Day] ) RETURN CALCULATE ( AVERAGEX ( VALUES ( 'Table'[Month] ); CALCULATE ( SUM ( 'Table'[Sales] ) ) ); FILTER ( ALL ( Table ); [Calendar Day] <= LastDate_ && [Calendar Day] > DATEADD ( LastDate_; -3; MONTH ) ) )

Similar to yours, but i changed the table for AVERAGEX to iterate over to the month values. Also changed the calcualte filter a little bit.

Re: Rolling average last 3 months

jayjay0306 — Wed, 19 Feb 2020 11:33:11 GMT

Hi Ulf,

Brilliant! it works. 🙂

Thanks a lot.

The only remark I have is, that the rolling average doesn't react to the filters I have in the PBI report.

The value "Table[Sales]" is part of you calculation, but if I fx.filter on "Sales area" the "Sales" per month respond accordingly, but the rolling average remains the same. I find this a bit strange.

Can you tell me why?any ideas?

Thanks anyway.

Br,

Jakob

Re: Rolling average last 3 months

Anonymous — Wed, 19 Feb 2020 12:30:43 GMT

Yes, it's because of ALL(Table) in the filter. All filters are removed to be able to access previous months. It should be possible to modify the CALCULATE / FILTER expression to only remove the filters on date/year/month. To do that easier and better I suggest creating a connected date table instead of having the date columns in the "fact" table. Dimensional models are usually easier to work with in Power BI! Then you can do the same thing as now but on the date table - ALL([NewDateTable]) instead of ALL(Table).

Re: Rolling average last 3 months

jayjay0306 — Thu, 20 Feb 2020 08:30:27 GMT

Excellent Ulf, that solved it 🙂

Thanks.

Re: Rolling average last 3 months

jayjay0306 — Thu, 20 Feb 2020 08:33:34 GMT

Hi Greg,

thanks for your input. much appreciated. The solution didn't quite solve my problem, but I got wiser on DAX. 🙂

Re: Rolling average last 3 months

Anonymous — Thu, 20 Feb 2020 12:23:27 GMT

Good to hear the problem is solved! By the way, you should mark my answer as the answer, not your own post... 🙂

Re: Rolling average last 3 months

arutsjak90 — Thu, 28 Jan 2021 10:07:51 GMT

Hi, I have similar issue, I tried to modify you DAX but I have received circular error, could you help to solve it? I have changed first line after FILTER because I need to calculate rolling 3months average per Plant. My column Date, contains "real" date dd/mm/yyyy, (first day of each month)

3MonthsRollingAverage =

VAR LastDate_ =

LASTDATE ( COGSTotal[Date])

RETURN

CALCULATE (

AVERAGEX ( VALUES (COGSTotal[Date]), CALCULATE ( SUM ( COGSTotal[Total COGS] ) ) ),

FILTER (

COGSTotal,

COGSTotal[Plant]=EARLIER(COGSTotal[Plant]) &

[Date] <= LastDate_

&& [Date] > DATEADD ( LastDate_, -3, MONTH )) )

Re: Rolling average last 3 months

WallyWallWal — Wed, 10 Mar 2021 22:08:15 GMT

I am not having any luck with this formula. It keeps erroring out on me and I can't figure out where I am going wrong. Trying to do 3-month rolling (hopefully dynamic) average. For month 2021-02, 3 month average should be 15.3% Can provide more data if needed. Any help or direction would be welcomed. Thanks!

Re: Rolling average last 3 months

jytech — Sat, 17 Jul 2021 05:47:53 GMT

Anonymous what if I have a separate Date Table separate from my Facts Table?

Re: Rolling average last 3 months

Anonymous — Sun, 18 Jul 2021 09:19:54 GMT

jytech, it should be the same as long as you have set up a relationship between the tables. This is the preferred way to set up the model. Then filter the date table instead of the fact table in the measure.

Re: Rolling average last 3 months

EVIJ — Wed, 11 May 2022 10:25:29 GMT

I am struggling with the same issue. Do you have a solution yet?

Re: Rolling average last 3 months

WallyWallWal — Wed, 11 May 2022 18:33:43 GMT

EVIJ I ended up having to ask a coworker who has a brain that works like magic. He suggested creating a CTE that joins the date table to itself. Below is the code. Not sure if this will help, but maybe it will give you some insight. It did work for my purposes.

WITH ABC AS
(
SELECT
[SurveyCompletedOn]

, case when [Answer] = 'Not at all likely - 0' then 'Detractor'
when [Answer] = '1' then 'Detractor'
when [Answer] = '2' then 'Detractor'
when [Answer] = '3' then 'Detractor'
when [Answer] = '4' then 'Detractor'
when [Answer] = '5' then 'Detractor'
when [Answer] = '6' then 'Detractor'
when [Answer] = '7' then 'Passive'
when [Answer] = '8' then 'Passive'
when [Answer] = '9' then 'Promotor'
when [Answer] = 'Extremely likely - 10' then 'Promotor'
end as 'NPS Scoring'

, ROW_NUMBER()OVER (ORDER BY [SurveyCompletedOn]) AS LINE

FROM [Database]

WHERE [Question] LIKE @SURVEY_QUESTION
and [SurveyCompletedOn] is not null

, NUMBER2 AS
(
SELECT
CONVERT(VARCHAR(7),DATEADD(MONTH,-2,ABC1.SurveyCompletedOn),23) AS '3 MONTH BEGIN'
, CONVERT(VARCHAR(7),ABC1.SurveyCompletedOn,23) AS '3 MONTH END'
, ABC2.[NPS SCORING] AS 'NPS SCORING'
, COUNT(DISTINCT(ABC2.[LINE])) AS 'SCORING'

FROM ABC AS ABC1
LEFT JOIN ABC AS ABC2
ON CONVERT(VARCHAR(7),ABC2.SurveyCompletedOn,23) BETWEEN CONVERT(VARCHAR(7),DATEADD(MONTH,-2,ABC1.SurveyCompletedOn),23)

AND CONVERT(VARCHAR(7),ABC1.SurveyCompletedOn,23)

GROUP BY

CONVERT(VARCHAR(7),DATEADD(MONTH,-2,ABC1.SurveyCompletedOn),23)
, CONVERT(VARCHAR(7),ABC1.SurveyCompletedOn,23)
, ABC2.[NPS Scoring]

)

SELECT
NUM2.[3 MONTH END]
, SUM(CASE WHEN NUM2.[NPS SCORING] = 'PROMOTOR' THEN NUM2.SCORING ELSE NULL END) AS 'PROMOTOR'
, SUM(CASE WHEN NUM2.[NPS SCORING] = 'DETRACTOR' THEN NUM2.SCORING ELSE NULL END) AS 'DETRACTOR'
, CONVERT(NUMERIC(18,2),SUM(CASE WHEN NUM2.[NPS SCORING] = 'PROMOTOR' THEN NUM2.SCORING ELSE NULL END)) / (SUM(NUM2.SCORING)) AS 'PROMOTER 3MTH PERCENT'--
, CONVERT(NUMERIC(18,2),SUM(CASE WHEN NUM2.[NPS SCORING] = 'DETRACTOR' THEN NUM2.SCORING ELSE NULL END)) / (SUM(NUM2.SCORING)) AS 'DETRACTOR 3MTH PERCENT'--

,(CONVERT(NUMERIC(18,2),SUM(CASE WHEN NUM2.[NPS SCORING] = 'PROMOTOR' THEN NUM2.SCORING ELSE NULL END)) / (SUM(NUM2.SCORING)) - CONVERT(NUMERIC(18,2),SUM(CASE WHEN NUM2.[NPS SCORING] = 'DETRACTOR' THEN NUM2.SCORING ELSE NULL END)) / (SUM(NUM2.SCORING))) AS 'NPS ROLLING 3-MONTH AVERAGE PERCENTAGE' --

FROM NUMBER2 NUM2

GROUP BY
NUM2.[3 MONTH END]

Re: Rolling average last 3 months

JLincoln — Thu, 23 Jun 2022 13:10:38 GMT

The Semicolons in the formula should be commas if you're working on it in America. Different syntax.

Re: Rolling average last 3 months

yash09 — Fri, 30 Aug 2024 10:15:38 GMT

i m using dax for rolling avg 3 month and that gets is result

Total sales 3MA = AVERAGEX(

WINDOW(

-[NR MONTHS Value],REL,0,REL,

SUMMARIZE(

ALLSELECTED(dimDate),

dimDate[Year],

dimDate[Month],

dimDate[Month Number]

ORDERBY(dimDate[Year],ASC,dimDate[Month],ASC)

),[TOTAL SALES]

)