Rolling average when you don't have full date

[Anonymous]

Hi

I've trying to calculate the 3,6,9 months rolling average for my data. The challenge I have is that transaction dates are all dated to be 1st of each month. To elaborate more, my data looks like the below. I was wondering how can I calculate the rolling averages in such condition? The sample data in this link

Thanks in advance

Date	Customer	QTY
1-Jan-21	A	200
1-Jan-21	B	300
1-Jan-21	C	400
1-Jan-21	D	500
1-Jan-21	C	100
1-Jan-21	B	100
1-Jan-21	A	200
1-Jan-21	C	300
1-Jan-21	D	500
1-Jan-21	D	100
1-Jan-21	C	300
1-Feb-21	A	700
1-Feb-21	B	600
1-Feb-21	D	150
1-Feb-21	A	250

[tackytechtom]

Hi @Anonymous ,

We need to remove filters from the context because otherwise the date filters to calculate the rolling x months will not be able to kick in. You could have removed all Filters by using ALL ( ), but then the rolling window would not have been applied properly when doing groupings on customer level. I assumed, that you would have liked to still be able to do such analysis. Hence, I just removed all filters except the one for customers. Another possible way could have been to explicitly remove the filter from dates, like this:

TomsRolling3MonthMeasureWithoutAllExcept = 
CALCULATE (
    AVERAGE (Table[QTY] ),
    REMOVEFILTERS ( Table[Date] ),
    DATESINPERIOD (
        'date'[Dates],
        EOMONTH ( LASTDATE ( Table[Date] ), 0),
        -3,
        MONTH
    )
)

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Anonymous ,

You are right, I did not read your query properly. I'll update the response above. 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Anonymous ,

How about this:

Here the code for the measures:

TomsRolling3MonthMeasure = 
CALCULATE (
    AVERAGE (Table[QTY] ),
    ALLEXCEPT ( 'Table', Table[Customer] ),
    DATESINPERIOD (
        'date'[Dates],
        EOMONTH ( LASTDATE ( Table[Date] ), 0),
        -3,
        MONTH
    )
)

TomsRolling6MonthMeasure = 
CALCULATE (
    AVERAGE (Table[QTY] ),
    ALLEXCEPT ( 'Table', Table[Customer] ),
    DATESINPERIOD (
        'date'[Dates],
        EOMONTH ( LASTDATE ( Table[Date] ), 0),
        -6,
        MONTH
    )
)

TomsRolling9MonthMeasure = 
CALCULATE (
    AVERAGE (Table[QTY] ),
    ALLEXCEPT ( 'Table', Table[Customer] ),
    DATESINPERIOD (
        'date'[Dates],
        EOMONTH ( LASTDATE ( Table[Date] ), 0),
        -9,
        MONTH
    )
)

Note, it is important to have a gapless date dimension in place.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

another question

From what I see, I require to create dates table. Which is fine.

May I know why I need to exclude the customer from the context filter?:

ALLEXCEPT ( 'Table', Table[Customer] )

On another note, I don't want the average per customer, but per month. I'll add a filter for customers (Slicer)

[Anonymous]

thanks @tackytechtom 

For average, shouldn't the DAX use Averagex/average instead of Sum?

CALCULATE (
    AVERAGE (Table[QTY] ),
    ALLEXCEPT ( 'Table', Table[Customer] ),
    DATESINPERIOD (
        'date'[Dates],
        EOMONTH ( LASTDATE ( Table[Date] ), 0),
        -9,
        MONTH
    )
)

[Anonymous]

@tackytechtom 

Something seems to be not right.

The 3 months and 6 months rolling average is the same.

also, the rolling average doesn't look right. 3 months rolling average on April-2019 should be

I think the fact that all transactions being on 1st of each month is contributing to this and it requires special processing.

The right values for example for 2019

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.