Rolling Average for past 6 months

[Anonymous]

Dear All,

I have this simple scenario where i need to calculate a rolling past 6 months Average for a given data set, below details.

Data Set Example:

The Year_Month column, in data source only had year and month, and i converted it to Data Type Date/Time n Power BI, hence shows Date as 1st date of that month.

I don't want to go the date level in the data source as it will be millions of rows of data.

Calendar Year_Month	Month	Query	Distinct User
1/12/2019	12	DSA	10
1/01/2020	1	DSA	1
1/02/2020	2	DSA	20
1/03/2020	3	DSA	10
1/04/2020	4	DSA	30
1/05/2020	5	DSA	40
1/12/2019	12	Sales	23
1/01/2020	1	Sales	21
1/02/2020	2	Sales	20
1/03/2020	3	Sales	9
1/04/2020	4	Sales	8
1/05/2020	5	Sales	10
1/12/2019	12	Stock	33
1/01/2020	1	Stock	41
1/02/2020	2	Stock	39
1/03/2020	3	Stock	21
1/04/2020	4	Stock	20
1/05/2020	5	Stock	11

DAX Formula used:

I have created a Date table in Power BI and referring that in the DATESINPERIOD function below.

DIVIDE(CALCULATE (
SUM(Rolling_Average_Example[Distinct User]),
DATESINPERIOD ( 'Date Table'[Date], MAX(Rolling_Average_Example[Calendar Year_Month]) , -6, month )
),6,0)

Expected Result:

For month of May, the rolling 6 month Average should be Sum of users in past 6 months/6 = 306/6 = 51.
But the DAX above shows 10, which is SUM of users in MAY divided by 6 (61/6=10), which is wrong.

If you can guide me where i am wrong and how to fix this in given scenario.

[v-gizhi-msft · Accepted solution]

Hi,

Please create a What If parameter first.

Then try this measure:

Measure = 
AVERAGEX (
    DISTINCT ( 'Table'[Calendar Year_Month] ),
    CALCULATE (
        IF (
            YEAR ( MAX ( 'Table'[Calendar Year_Month] ) ) = YEAR ( TODAY () )
                && MONTH ( MAX ( 'Table'[Calendar Year_Month] ) ) = MONTH ( TODAY () ),
            0,
            IF (
                MONTH ( TODAY () ) - Previous[Previous Value] <= 0,
                CALCULATE (
                    SUM ( 'Table'[Distinct User] ),
                    FILTER (
                        'Table',
                        'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
                            && 'Table'[Calendar Year_Month]
                                >= DATE ( YEAR ( TODAY () ) - 1, 12
                                    - ABS ( MONTH ( TODAY () ) - Previous[Previous Value] ), 1 )
                    )
                ),
                CALCULATE (
                    SUM ( 'Table'[Distinct User] ),
                    FILTER (
                        'Table',
                        'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
                            && 'Table'[Calendar Year_Month]
                                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - Previous[Previous Value], 1 )
                    )
                )
            )
        )
    )
)

When select one value in slicer, the result shows:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

@Anonymous , please find the attached pbix after the signature. I am getting 50.

[Anonymous]

@amitchandak  Thanks for your response, the sample data i posted is in format dd/mm/yyyy, so it is actually user counts for different months of the year 2019 and 2020, sorry i should have been clear with format.

Attached screenshot comparing how the data looks on my side and what i could see in the pbix file shared by you (left hand side is data on my side)

Rolling Average_Comparison 

[amitchandak]

@Anonymous , now I am getting 60. What should not be there in 6 months?

File attached after signature

[Anonymous]

@amitchandak , Based on the dataset i shared, i want to calculate the rolling 6 months average, i took May as example, in initial post what is the expected value of rolling 6 months average as compared to what is happening now.