dax for rolling average of last 3 months excluding current month

[nthalkar]

I need one help from you. I am stuck in one logic..

i am trying to get correct dax for rolling average of last 3 months excluding current month

i have  tried following dax 

Rolling Average =

IF(

    ISFILTERED('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -2, MONTH)),

            __LAST_DATE

        )

    RETURN

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('ZCUSTSALE 01 04 2021 TO LATEST'),

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Year],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[QuarterNo],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Quarter],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[MonthNo],

                    'ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(

                SUM('ZCUSTSALE 01 04 2021 TO LATEST'[ Sale Value]),

                ALL('ZCUSTSALE 01 04 2021 TO LATEST'[MONTH].[Day])

            )

        )

)

as per above dax system will take current month for average calculation .

i need rolling average for last 3 month excluding current month. in the month of Oct . Bi should take Sep aug July value for average

Please help to correct above dax for correct result

[v-yanjiang-msft]

Hi @nthalkar ,

According to your description, I create a sample , you can try like this.

1.Create a calendar table

Table2 =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), TODAY () ),
    "YEARMONTH",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

2.Rank it by month.

RANK=RANKX('Table2',[YEARMONTH],,DESC,Dense)

3.Create relationship.

4.Create a measure to calculate the average.

Measure =
CALCULATE (
    AVERAGE ( 'Table'[Sales] ),
    FILTER ( 'Table2', [RANK] IN { 2, 3, 4 } )
)

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nthalkar]

Thank You..

I have made same table and put logic as per you shared but i cant make releationship one to one due to this possibly answer is not coming right

demo Excel attached 

MONTH	Stockist Code.	Stockist Name.	Material Desc	Division	Plant	Sale Value
Apr-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	3,016.66
Apr-21	10013	LALCO PHARMA (P) LTD.	Cosvate - G Cream 20g Lami tube	30	DLHI	3,645.00
Apr-21	10013	LALCO PHARMA (P) LTD.	Amrolstar Nail Lacquer 2.5ml vials	21	DLHI	2,565.00
Apr-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next Roll-on 50ml	21	DLHI	1,826.87
Jun-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	1,508.33
Jun-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next Roll-on 50ml	21	DLHI	1,826.87
Jul-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	3,016.66
Aug-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	3,016.66
Sep-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	4,524.99
Sep-21	10013	LALCO PHARMA (P) LTD.	Amrolstar Nail Lacquer 2.5ml vials	21	DLHI	1,410.07
Oct-21	10013	LALCO PHARMA (P) LTD.	Cosmelite Next cream 30g	21	DLHI	9,643.11
Oct-21	10013	LALCO PHARMA (P) LTD.	Amrolstar Nail Lacquer 2.5ml vials	21	DLHI	2,820.15

[v-yanjiang-msft · Accepted solution]

Hi @nthalkar ,

You don't have to make one to one relationship, just drag date column in one table to another, it will automatically create relationship. I modify my sample and it works fine.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h2000waley]

Hi All, I found the Rolling Average in "quick Measure" of PBI is not correct. There are more than 1 way to create the rolling average DAX, that we could find from different websites. Below one is what I am using. For your reference:

Step 1, you need a Date table (e.g.: 'Calendar'[Date]). Also can be found via different website.

Step 2, Create a Measure for your desired value. E.g.:

Actual =

CALCULATE ( SUM ('Sales'[Order subTotal],

USERELATIONSHIP('Calendar'[Date], 'Sales'[Order booked date])

)

Step 3, Create a Measure for rolling average as below:

Actual Rolling Avg 3M =

VAR NumOfMonths = 3

VAR LastCurrentDate =

MAX ( 'Calendar'[Date])

VAR Period =

DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )

VAR Result =

CALCULATE (

AVERAGEX (

VALUES ( 'Calendar'[YearMonth] ),

[Actual]

),

Period

)

VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] )

VAR LastDateWithSales = MAX ( 'Sales'[Order booked date] )

RETURN

IF ( FirstDateInPeriod <= LastDateWithSales, Result )

Step 4: Create a Measure to excluding the current month as below:

Actual Rolling Avg 3CalM =

CALCULATE([Actual Rolling Avg 3M],

PREVIOUSMONTH('Calendar'[Date]))

Step 5: at thie moment, you can already use Step 4 measure to create a table with Month and Rolling Average. But if you need to show this as Card Visual, you need one more step here. Create a Measure as below: 

Card for Actual Rolling Avg 3CalM =

var _max = maxx('Calendar','Calendar'[Month])

return

calculate([Actual Rolling Avg 6CalM], filter('Calendar','Calendar'[Month] =_max ))
------------Done--------------

Today is 2022/06/17. Here below is my data capture for your reference:

[Greg_Deckler]

@nthalkar Should be able to use a slightly modified rolling months:

Rolling Months - Microsoft Power BI Community

[nthalkar]

Thank You Greg..

Can you suggest me if any correction or modification in my Dax