Rolling average for the past 3 months ( have to be full month)

[leilei787]

Hello:

i am trying to figure out how to calculate rolling past 3 months average. but they need to be full month....for example, today is May 12th...so the calculation should do an average of April, March and Feb ( excluding May because it is not completed yet)

Below is my data sample....let's say i want to know rolling average for the past 3 months for Pencil.

thank you in advance!!!! 

OrderDate	Item	Units
1/1/2022	Pencil
1/2/2022	Binder	50
1/3/2022	Pencil	36
1/4/2022	Pen	27
1/5/2022	Pencil	56
1/6/2022	Binder	60
1/7/2022	Pencil	75
1/8/2022	Pencil	90
1/9/2022	Pencil	32
1/10/2022	Binder	60
1/11/2022	Pencil	90
1/12/2022	Binder	29
1/13/2022	Binder	81
1/14/2022	Pencil	35
1/15/2022	Desk
1/16/2022	Pen Set	16
1/17/2022	Binder	28
1/18/2022	Pen	64
1/19/2022	Pen	15
1/20/2022	Pen Set
1/21/2022	Pencil	67
1/22/2022	Pen Set	74
1/23/2022	Binder	46
1/24/2022	Binder	87
1/25/2022	Binder
1/26/2022	Binder
1/27/2022	Pen Set	50
1/28/2022	Pencil	66
1/29/2022	Pen
1/30/2022	Pencil	53
1/31/2022	Binder	80
2/1/2022	Desk
2/2/2022	Pen Set	62
2/3/2022	Pen Set	55
2/4/2022	Pen Set	42
2/5/2022	Desk
2/6/2022	Pencil
2/7/2022	Pen	76
2/8/2022	Binder	57
2/9/2022	Pencil	14
2/10/2022	Binder	11
2/11/2022	Binder
2/12/2022	Binder	28
2/13/2022	Pencil
2/14/2022	Binder
2/15/2022	Pencil	22
2/16/2022	Pen
2/17/2022	Pencil	44
2/18/2022	Binder
2/19/2022	Pencil	52
2/20/2022	Pencil
2/21/2022	Pencil
2/22/2022	Binder	53
2/23/2022	Pencil
2/24/2022	Binder	25
2/25/2022	Binder
2/26/2022	Pencil	52
2/27/2022	Desk	55
2/28/2022	Pen Set	3
3/1/2022	Binder	5
3/2/2022	Pen	8
3/3/2022	Pen	6
3/4/2022	Pen Set
3/5/2022	Pencil	85
3/6/2022	Pen Set	47
3/7/2022	Binder	64
3/8/2022	Binder
3/9/2022	Binder	77
3/10/2022	Binder
3/11/2022	Pen Set
3/12/2022	Pencil
3/13/2022	Pen	47
3/14/2022	Pencil
3/15/2022	Binder
3/16/2022	Desk	47
3/17/2022	Pen Set	99
3/18/2022	Pen Set	5
3/19/2022	Pen Set
3/20/2022	Desk	5
3/21/2022	Pencil
3/22/2022	Pen	7
3/23/2022	Binder	74
3/24/2022	Pencil
3/25/2022	Binder
3/26/2022	Binder	50
3/27/2022	Binder	36
3/28/2022	Pencil	27
3/29/2022	Binder	56
3/30/2022	Pencil	60
3/31/2022	Pen	75
4/1/2022	Pencil	90
4/2/2022	Binder	32
4/3/2022	Pencil	60
4/4/2022	Pencil	90
4/5/2022	Pencil	29
4/6/2022	Binder	81
4/7/2022	Pencil	35
4/8/2022	Binder
4/9/2022	Binder	16
4/10/2022	Pencil	28
4/11/2022	Desk	64
4/12/2022	Pen Set	15
4/13/2022	Binder
4/14/2022	Pen	90
4/15/2022	Pen	29
4/16/2022	Pen Set	90
4/17/2022	Pencil	29
4/18/2022	Pen Set
4/19/2022	Binder	29
4/20/2022	Pen Set
4/21/2022	Pencil
4/22/2022	Pen Set	29
4/23/2022	Binder	90
4/24/2022	Pen Set	29
4/25/2022	Pencil
4/26/2022	Pen Set	29
4/27/2022	Binder
4/28/2022	Pen Set
4/29/2022	Pencil	29
4/30/2022	Pen Set	90
5/1/2022	Binder	29
5/2/2022	Pen Set
5/3/2022	Pencil	29
5/4/2022	Pen Set
5/5/2022	Binder	22
5/6/2022	Pen Set	33

@tamerj1 @Greg_Deckler 

[Ashish_Mathur · Accepted solution]

You are welcome.  If my original reply helped, please mark it as Answer.  ABCD is just the title of the new column which will get created in the virtual table (via the SUMMARIZE() function). 

[tamerj1]

HI @leilei787 
Here is a sample file with the solution https://www.dropbox.com/t/23vxVrFExwxu0xUw

Rolling Average = 
VAR CurrentDate = 
    MAX ( 'Date'[Date] )
VAR StartofPeriod =
    EOMONTH ( CurrentDate, -4 ) + 1
VAR EndOfPeriod =
    EOMONTH ( CurrentDate, -1 )
VAR Result =
    CALCULATE ( 
        AVERAGE ( Sales[Units] ),
        'Date'[Date] >= StartofPeriod,
         'Date'[Date] <= EndOfPeriod
    )
RETURN
    Result

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[leilei787]

Thank you Ashish!

i review the PBI you sent...It works except i changed the formula a bit so that it reflects past 3 completed month, not 2. 

Question, in your formula, what is that "ABCD" represent?

Average units sold in past 3 completed months = averagex(SUMMARIZE(CALCULATETABLE('Calendar',DATESBETWEEN('Calendar'[Date],edate(EOMONTH(today(),-1)+1,-3),EOMONTH(today(),-1))),'Calendar'[Year],'Calendar'[Month name],"ABCD",[Units sold]),[ABCD])

thanks for your time

[tamerj1]

Apologies for misunderstanding your requierement. Here is the modified version of my solution https://www.dropbox.com/t/sqjAtA5drCIicKZk

Rolling Average = 
VAR CurrentDate = 
    MAX ( 'Date'[Date] )
VAR StartofPeriod =
    EOMONTH ( CurrentDate, -4 ) + 1
VAR EndOfPeriod =
    EOMONTH ( CurrentDate, -1 )
VAR Result =
    CALCULATE ( 
        AVERAGEX (
            VALUES ( 'Date'[Month] ),
            CALCULATE ( SUM ( Sales[Units] ) )
        ),
        'Date'[Date] >= StartofPeriod,
        'Date'[Date] <= EndOfPeriod
    )
RETURN
    Result

[leilei787]

@tamerj1  hi Tamerj1, thank you again for your time!

I open up the PBI you attached....somehow i am still seeing the same result. (see screenshot)....is it the right file? thanks

[tamerj1]

@leilei787 

maybe was no saved when uploaded. I'm sending this from the phone. I hope it is the correct one. 
https://www.dropbox.com/transfer/cap_pid_ft:AAAAABKFFQJMawa4rlv6E90_JwaV6XsJl7LAFMJZeqiKUOApi3ZVcn0

[leilei787]

@tamerj1 hi Tamerj1, still not quite....i swear this is the one you just sent...still not the right one 

[tamerj1 · Accepted solution]

Hi @leilei787 
I'm using DropBox on different machines. The file was open and unsaved on this machine. Sory for that. Here is a lnik on WeTransfer just to make sure you recieve the correct one.

https://we.tl/t-nsW7l0Jekq

[leilei787]

@tamerj1thank you! this file works. two question:

i see you added "Date" table, basically a time table...is it necessary? can i just use the order date in my original table? ( i tried not to add additional time table to my real dataset)

if additional time table is needed....is your time based on excel file or using some kind of formula? i want to be able to replicate the table if need to

2nd question, i need to do 3 months moving average for the past 2 years...so let's say Apr 2022, i need to average Apr, Mar and Feb; Then Mar 2022, i need to average Mar, Feb and Jan; for Feb 2022, i need to average Feb, Jan and Dec 2021....does your solution work cross years? ( no partial month)

i appreciate for your time!

[tamerj1]

@leilei787 

Yes it works accross years. This is why I added a date table. This is a date table not time table. It is created using dax code. Just click on New table snd paste the code. It is there in pbi file. 

[leilei787]

@tamerj1 thank you Tamerj1, you have taught me quite a few things. appreciate!

After some reviews, your formula works! The only limitation is that if the unit is 0 for that month, the formula skip the month and divide the total by 2 instead of 3. for example, if Pen sold 10 unit in Jan, 0 unit in Feb and 20 unit in Mar, the result become (10+20)/2=15. it should be (10+20)/3=10. 

i am using the data below...could you please see if we can capture the month even though 0 unit sold? appreciate again!

OrderDate	Item	Units
1/4/2021	Pen	27
1/15/2021	Desk
1/18/2021	Pen	64
1/19/2021	Pen	15
1/29/2021	Pen
2/1/2021	Desk
2/5/2021	Desk
2/7/2021	Pen	76
2/16/2021	Pen
2/27/2021	Desk	55
3/2/2021	Pen	8
3/3/2021	Pen	6
3/13/2021	Pen	47
3/16/2021	Desk	47
3/20/2021	Desk	5
3/22/2021	Pen	7
3/31/2021	Pen	75
4/11/2021	Desk	64
4/14/2021	Pen	90
4/15/2021	Pen	29
5/8/2021	Pen
5/11/2021	Desk
5/15/2021	Desk
5/17/2021	Pen	76
5/26/2021	Pen
6/6/2021	Desk	55
6/9/2021	Pen	8
6/10/2021	Pen	6
6/20/2021	Pen	47
6/23/2021	Desk	47
6/27/2021	Desk	5
6/29/2021	Pen	7
7/8/2021	Pen	75
7/19/2021	Desk	64
7/22/2021	Pen	90
7/23/2021	Pen	29
8/15/2021	Pen
8/18/2021	Desk
8/22/2021	Desk
8/24/2021	Pen	76
9/2/2021	Pen
9/13/2021	Desk	55
9/16/2021	Pen	8
9/17/2021	Pen	6
9/27/2021	Pen	47
9/30/2021	Desk	47
10/4/2021	Desk	5
10/6/2021	Pen	7
10/15/2021	Pen	75
10/26/2021	Desk	64
10/29/2021	Pen	90
10/30/2021	Pen	29
11/22/2021	Pen
11/25/2021	Desk
11/29/2021	Desk
12/1/2021	Pen	76
12/10/2021	Pen
12/21/2021	Desk	55
12/24/2021	Pen	8
12/25/2021	Pen	6
1/4/2022	Pen	47
1/7/2022	Desk	47
1/11/2022	Desk	5
1/13/2022	Pen	7
1/22/2022	Pen	75
2/2/2022	Desk	64
2/5/2022	Pen	90
2/6/2022	Pen	29
3/1/2022	Pen
3/4/2022	Desk
3/8/2022	Desk
3/10/2022	Pen	76
3/19/2022	Pen
3/30/2022	Desk	55
4/2/2022	Pen	8
4/3/2022	Pen	6
4/13/2022	Pen	47
4/16/2022	Desk	47
4/20/2022	Desk	5
4/22/2022	Pen	7
5/1/2022	Pen	75

[tamerj1]

Hi @leilei787 

this how AVERAGE behaves in case od blanks. Please try (in case you have 3 consecutive months with no sales use DIVIDE function instead of "/" operator. 

Rolling Average = 
VAR CurrentDate = 
    MAX ( 'Date'[Date] )
VAR StartofPeriod =
    EOMONTH ( CurrentDate, -4 ) + 1
VAR EndOfPeriod =
    EOMONTH ( CurrentDate, -1 )
VAR Result =
    CALCULATE ( 
        SUMX (
            VALUES ( 'Date'[Month] ),
            CALCULATE ( SUM ( Sales[Units] ) )
        ),
        'Date'[Date] >= StartofPeriod,
        'Date'[Date] <= EndOfPeriod
    )/3
RETURN
    Result

[leilei787]

@tamerj1 

totally awesome! thank you!

[Ashish_Mathur · Accepted solution]

You are welcome.  If my original reply helped, please mark it as Answer.  ABCD is just the title of the new column which will get created in the virtual table (via the SUMMARIZE() function).