Rolling 12 month for sum qty

[rachaelwalker]

I would like to verify that I am using the right calculation to create a Rolling 12 month average on QTY. My data set is below and formula. I want to use this to create a line value on a bar chart similar to below

This is the formula I found searching google

R12M Sell_Quote_Qty = 
CALCULATE(
	SUM('Sell Quotes'[Quantity]), 
	DATESINPERIOD('Sell Quotes'[Opp Close Date], LASTDATE('Sell Quotes'[Opp Close Date]), -12, Month)
)
/
CALCULATE(
	DISTINCTCOUNT('Sell Quotes'[Opp Close Date]),
	DATESINPERIOD('Sell Quotes'[Opp Close Date], LASTDATE('Sell Quotes'[Opp Close Date]), -12, MONTH)
)

Opp Close Date	Quantity
1/7/2021	2
2/24/2021	2
3/15/2021	23
3/25/2021	1
3/26/2021	4
3/30/2021	5
3/31/2021	13
4/8/2021	25
4/13/2021	19
4/14/2021	114
4/23/2021	24
4/26/2021	333
4/27/2021	30
4/28/2021	32
4/29/2021	68
4/30/2021	225
5/12/2021	649
5/20/2021	340
5/21/2021	114
5/24/2021	7
5/26/2021	46
5/27/2021	96
6/2/2021	55
6/3/2021	11
6/4/2021	9
6/8/2021	1
6/9/2021	9
6/10/2021	33
6/15/2021	16
6/17/2021	25
6/23/2021	10
6/24/2021	287
6/29/2021	602
7/6/2021	233
7/8/2021	107
7/9/2021	7
7/13/2021	16
7/14/2021	119
7/16/2021	14
7/19/2021	10
7/21/2021	47
7/26/2021	212
7/27/2021	45
7/28/2021	159
7/29/2021	82
7/30/2021	42
8/2/2021	83
8/3/2021	111
8/5/2021	884
8/6/2021	147
8/10/2021	29
8/11/2021	13
8/12/2021	4
8/16/2021	1
8/17/2021	58
8/18/2021	422
8/19/2021	26
8/20/2021	4
8/24/2021	20
8/25/2021	20
8/26/2021	6
8/27/2021	18
8/31/2021	87
9/1/2021	44
9/3/2021	27
9/8/2021	606
9/9/2021	7
9/10/2021	298
9/13/2021	539
9/14/2021	6
9/15/2021	76
9/17/2021	94
9/22/2021	19
9/23/2021	688
9/27/2021	1013
9/28/2021	23
9/29/2021	525
9/30/2021	52
10/1/2021	4
10/5/2021	685
10/6/2021	38
10/8/2021	14
10/12/2021	345
10/13/2021	117
10/14/2021	77
10/15/2021	265
10/18/2021	111
10/19/2021	35
10/20/2021	361
10/21/2021	6
10/22/2021	3024
10/25/2021	407
10/26/2021	201
10/27/2021	256
10/28/2021	1664
10/29/2021	113
10/31/2021	191
11/1/2021	109
11/2/2021	82
11/3/2021	234
11/9/2021	75
11/10/2021	128
11/11/2021	2717
11/12/2021	8
11/16/2021	331
11/18/2021	96
11/22/2021	148
11/23/2021	305
11/24/2021	25
11/30/2021	190
12/1/2021	42
12/2/2021	77
12/3/2021	876
12/8/2021	167
12/9/2021	151
12/10/2021	163
12/11/2021	28
12/13/2021	180
12/15/2021	141
12/16/2021	56
12/22/2021	21
12/24/2021	227
12/29/2021	8
12/30/2021	252
12/31/2021	6172
1/1/2022	27
1/3/2022	66
1/9/2022	47
1/10/2022	53
1/12/2022	51
1/16/2022	303
1/28/2022	83
1/30/2022	28
1/31/2022	400
2/1/2022	39
2/3/2022	35
2/10/2022	15
2/12/2022	45
2/28/2022	297
3/1/2022	4
3/10/2022	5
6/30/2022	11

[Anonymous]

HI @rachaelwalker,

Here is the measure expression of the rolling 12 month average of 'Quantity' field, you can try if it helps:

R12M Sell_Quote_Qty =
VAR currDate =
    MAX ( 'Sell Quotes'[Opp Close Date] )
RETURN
    CALCULATE (
        AVERAGE ( 'Sell Quotes'[Quantity] ),
        FILTER (
            ALLSELECTED ( 'Sell Quotes' ),
            YEAR ( [Opp Close Date] )
                >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 12, DAY ( currDate ) )
                && [Opp Close Date] <= currDate
        )
    )

Regards,

Xiaoxin Sheng

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating Monthly-Quantity of 12month rolling average.

QTY total 12month rolling avg: =

VAR lastdateqty =

CALCULATE ( MAX ( 'Sell Quotes'[Opp Close Date] ), REMOVEFILTERS () )

VAR currentyearmonthsort =

MAX ( Dates[YearMonth Sort] )

VAR yearmonthtable =

SUMMARIZE (

FILTER (

ALL ( Dates ),

Dates[YearMonth Sort] <= currentyearmonthsort

&& Dates[YearMonth Sort] >= currentyearmonthsort - 11

),

Dates[Year & Month],

Dates[YearMonth Sort]

)

VAR addqty =

ADDCOLUMNS ( yearmonthtable, "@monthlyqty", [QTY Total:] )

RETURN

IF ( MIN ( Dates[Date] ) <= lastdateqty, AVERAGEX ( addqty, [@monthlyqty] ) )

[rachaelwalker]

@Jihwan_Kim  Thank you for taking the time to help me. This is very helpful. I am now using my date table and your measure but it is returning an error "Parameter is not correct type"

Additional information that may or may not be relevant:

1. My date table is not marked as a date table. Should I mark it as date table?

2. My date table is created in power query using a function. My date table did not have YearMonth and YearMonthSort so I created them using DAX.

[rachaelwalker]

I found if I use ( 'Calendar' ) on line 9, there are no more errors but it is returning the same values as qty. I have tried all the formulas in this thread and same result. My 'Sell Quote'[Opp Close Date] is a lookup column from another table. Would that impact this?

[rachaelwalker]

Your formula worked once I corrected my date table and used the date field from date table. Thank you for taking the time

[CNENFRNL]

R12M =
CALCULATE(
    SUM( 'Sell Quotes'[Quantity] )
        / DISTINCTCOUNT( 'Sell Quotes'[Opp Close Date] ),
    DATESINPERIOD(
        'Sell Quotes'[Opp Close Date],
        MAX( 'Sell Quotes'[Opp Close Date] ),
        -12,
        MONTH
    )
)