Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Matthew Wright]

Hello, 

I have created a rolling average of our sales in Powerbi. It calculates the average for the last 3 months. My challenge right now is that it is calculating a rolling average for the current month and the previous 2. Where I really want it to do is to ignore the current month and create an average for the past 3. For example, this month is January I want to give me the average for Oct, Nov, and Dec. 

This is what I am currently using: 

Moving X Months AVG = SUMX(DATESINPERIOD(DSS_DATA[Run_Date],LASTDATE(DSS_DATA[Run_Date]),-3,MONTH),[Total Internal Samples])/3

Any help would be greatly appreciated.

——————————
Matthew Wright
Business Director
9195046424
——————————

[Hasham Niaz]

Hi Matthew !

Use StartOfMonth() to find the start date of current month & then use DATEADD() function to get last day of previous month.

From there You can use the same Date to calculate past 3 months using already built DAX.

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Matthew Wright]

Hasham, 

Thanks. I created a measure to get the start of the previous month but its returning for me 12/1/2019. Also when I combine it with the current DAX measure the math is not working. 

PrevMonth = CALCULATE(STARTOFMONTH(Datestest[Date]),DATEADD(Datestest[Date],-1,MONTH))

Moving X TEST3 Months AVG = SUMX(DATESINPERIOD(DSS_DATA[Run_Date],[PrevMonth](DSS_DATA[Run_Date]),-3,MONTH),[Total Internal Samples])/3

——————————
Matthew Wright
Business Director
9195046424
——————————
——————————————-

[Hasham Niaz]

Hi !

It should be something on the lines;

VAR LastDayofPrevMonth = DATEADD(STARTOFMONTH('Calendar'[Date]), -1, DAY)

VAR FirstDayofLast3Month = DATEADD(STARTOFMONTH(LastDayofPrevMonth), -2, MONTH)

Now try to implement using above logic.

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Matthew Wright]

Hasham, 

I appreciate your help. Apologies I am still very new to writing DAX and have only worked with VAR very little. How do I merge the two measures together? 

When I write the logic you suggested do I do that within the existing measure I wrote or do I need to create a separate measure?

——————————
Matthew Wright
Business Director
9195046424
——————————
——————————————-

[Matthew Wright]

I have tried the following but no luck: 

Moving X TEST3 Months AVG =

VAR LastDayOfPrevMonth = DATEADD(STARTOFMONTH(DSS_DATA[Run_Date]),-1,DAY)

VAR FirstDayofLastMonth = DATEADD(STARTOFMONTH(LastDayOfPrevMonth),-2,MONTH)

RETURN

SUMX(

DATESINPERIOD(DSS_DATA[Run_Date],LastDayOfPrevMonth,-2,MONTH),

[Total Internal Samples])/3

——————————
Matthew Wright
Business Director
9195046424
——————————
——————————————-

[Hasham Niaz]

Please share the sample file

Regards,

——————————
Hasham Bin Niaz
Director Data & Analytics
Karachi, Pakistan
——————————
——————————————-

[Sergio Murru]

Hi Mattew,

I think PARALLELPERIOD() could be what you are looking for

I hope this helps

——————————
Sergio Murru
TORINO
——————————
——————————————-

[Robert Lamb]

Hi Matthew

I found this article quite helpful – I adapted it to my needs (to calculate a moving range), but I have no idea *why* it works – good luck!

https://www.sqlbi.com/articles/comparing-with-previous-selected-time-period-in-dax/

Cheers

Robert

——————————
Robert Lamb
Lean Coach
——————————
——————————————-

[Matthew Wright]

Robert, 

This is helpful thank you very much

——————————
Matthew Wright
Business Director
9195046424
——————————
——————————————-

[Nicolas MENDEZ]

Hi Matthew,

You cab try the formula below:

Rolling AVG 3M =
CALCULATE (
    [Total Internal Samples],
    DATESBETWEEN (
        DSS_DATA[Run_Date].[Date],
        DATEADD ( STARTOFMONTH ( DSS_DATA[Run_Date] ), -3, MONTH ),
        STARTOFMONTH ( DSS_DATA[Run_Date] ) - 1
    )
) / 3
?

Writing this I assume that the [Total Internal Sample] is a measure computing the SUM of Internal Samples. If yes, the formula above should OK, if no, I need more infos about [Total Internal Sample] content

I hope it’s a good start for you

——————————
Nicolas MENDEZ
Data & BI consultant
Becom Consulting
——————————
——————————————-

[Matthew Wright]

Nicolas, 

That worked like a charm thank you so much! 

——————————
Matthew Wright
Business Director
9195046424
——————————
——————————————-

[Bhupendra Mishra]

Sir, can u assist in using AVERAGE func to re-write the same above

——————————
Anubhav Mahule
Lead consultant
——————————
——————————————-