Dax Beginner : Moving Average 5 periods -based on slicer date (from - to)

[Falongi_82]

Hi Guys,

I'm a Dax beginner and is my first post ;-). I need your help to find the way on how to make a moving average by 5 periods for a selected period (SLICER - field PICKING_DATE)

In fact, I try to have the same comportment as in excel when you add amoving avg line in a graph wit period 5.

So, I have the following Table :

==> important field (PICKING_DATE  and SAP_LINES)

PICKING_DATE GENERAL_FLOW ROUTE SAP_LINES
02/01/2020 End Customer RSL2 982
03/01/2020 End Customer RSL2 230
06/01/2020 End Customer RSL2 1426
07/01/2020 End Customer RSL2 1134
08/01/2020 End Customer RSL2 1078
09/01/2020 End Customer RSL2 1045
10/01/2020 End Customer RSL2 253
13/01/2020 End Customer RSL2 1418
14/01/2020 End Customer RSL2 930
15/01/2020 End Customer RSL2 859
16/01/2020 End Customer RSL2 1142
17/01/2020 End Customer RSL2 229
20/01/2020 End Customer RSL2 1284
21/01/2020 End Customer RSL2 962
22/01/2020 End Customer RSL2 843
23/01/2020 End Customer RSL2 888
24/01/2020 End Customer RSL2 249

If I select all the date in my slicer (based on picking_date), I want this result :

Picking_date Avg_5_period (Sap_lines)     Remarks
02/01/2020 blank                                  because we don't have 5 peiods before
03/01/2020 blank                                  because we don't have 5 peiods before
06/01/2020 blank                                  because we don't have 5 peiods before
07/01/2020 blank                                  because we don't have 5 peiods before
08/01/2020 970                                     (982+230+1426+1134+1078)/5
09/01/2020 983                                     (230+1426+1134+1078+1045)/5
10/01/2020 987 …
13/01/2020 985,6
14/01/2020 944,8
15/01/2020 901
16/01/2020 920,4
17/01/2020 915,6
20/01/2020 888,8
21/01/2020 895,2
22/01/2020 892
23/01/2020 841,2
24/01/2020 845,2
27/01/2020 860,8

If in my slicer, I select a period for ex. from 17/01 to 27/01, I want this result :

Picking_date          Avg_5_period (Sap_lines)
17/01/2020
20/01/2020
21/01/2020
22/01/2020
23/01/2020           841,2
24/01/2020           845,2
27/01/2020           860,8

I've tried many measure with averagex but unsuccessfuly ;-(

Hope you can help me,

Thx in advance

Falongi82

[Fowmy]

@Falongi_82 

Try this measure and confirm please:

Rolling 5 Periods Avg = 
VAR LAST5 = 
    TOPN(
        5,
        FILTER(
            ALL(Table01[PICKING_DATE]),
            Table01[PICKING_DATE]<=MAX(Table01[PICKING_DATE])),
            Table01[PICKING_DATE]
    )
RETURN
IF( 
    COUNTROWS(LAST5)<5,
    BLANK(),
    AVERAGEX(
        CALCULATETABLE(
            Table01,
            LAST5
        ),
        Table01[AMOUNT]
    )
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

// For such calculations you have to 
// have a DateTable in the model that
// stores full years without gaps
// and mark it as a Date table. The
// granularity of the table is the day,
// obviously.

[Rolling 5-Day Avg] =
var __lastVisibleDay = MAX( DateTable[Date] )
var __dateRange =
	CALCULATETABLE(
		VALUES( DateTable[Date] ),
		DateTable[Date] <= __lastVisibleDay,
		DateTable[Date] >= __lastVisibleDay - 4,			
		ALL( DateTable )
	)
var __shouldCalculate =
	// Do we have 5 days in the DateTable
	// going back?
	COUNTROWS( __dateRange ) = 5
return
	if( __shouldCalculate,
		AVERAGEX(
			__dateRange,
			// You have to add 0 to
			// [YourMeasure] if you
			// want to also treat
			// BLANK as 0 and include it
			// in the calculation of
			// the average. If you don't,
			// days where the measure is
			// BLANK are ignored.
			[YourMeasure]
		)
	)

[Greg_Deckler]

@Falongi_82  - Here is one measure to get you started. You can put this measure in a table visual along with PICK_DATE. 

Measure = 
    VAR __Date = MAX('Table'[PICK_DATE])
    VAR __Table = TOPN(5,FILTER(ALL('Table'),[PICK_DATE] <= __Date),[PICK_DATE],DESC)
RETURN
    IF(COUNTROWS(__Table)<5,BLANK(),AVERAGEX(__Table,[SAP_LINES]))

Since you are new, please check out this article: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Fowmy]

@Falongi_82 

Suppose you have a calendar table otherwise, you can create one using : https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Then, use the quick measure

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Falongi_82]

Thanks for your reply,

I only have 1 table with a column date (PICKING_DATE).

I've tried the rolling average but my problem is that I want to have an average for 5 periods available and not 5 consecutive date. 

This is my difficulty 😞

In the case below , I want to have for the 08/01/2020, the average of 2-3-6-7-8/01/2020.

If I choose in my slicer, picking_date from 07/01 to 16/01, my first average should start on 12/01/2020

thx in advance for the help

R/

F.

[Anonymous · Accepted solution]

Easy enough,

// Say that your DateTable is connected
// to your fact through the field PickingDate
// since this is the correct setup you should
// have.

[Rolling 5-Day Avg] =
var __lastVisibleDay = MAX( DateTable[Date] )
var __effectiveDates =
	TOPN(5,
		CALCULATETABLE(
			VALUES( FactTable[PickingDate] ),
			DateTable[Date] <= __lastVisibleDay,
			// ALL is here just in case you've
			// forgotten to mark the DateTable
			// as the Date table in the model.
			ALL( DateTable )
		),
		FactTable[PickingDate],
		DESC
	)
var __shouldCalculate =
	COUNTROWS( __effectiveDates ) = 5
return
	if( __shouldCalculate,
		CALCULATE(
			AVERAGEX(
				TREATAS(
					__effectiveDates,
					DateTable[Date]
				),
				// You have to add 0 to
				// [YourMeasure] if you
				// want to also treat
				// BLANK as 0 and include it
				// in the calculation of
				// the average. If you don't,
				// days where the measure is
				// BLANK are ignored.
				[YourMeasure]
			),
			// Same as above - this filter is
			// not necessary if DateTable
			// is marked as the Date table in
			// the model.
			ALL( DateTable )
		)
	)

[Falongi_82]

@Fowmy, @Anonymous  :

Good news, It works  THX 👍

I've created as recommended a date table 

==> DateTable = CALENDARAUTO() - linked this field with my picking date and adapt your measure as follow :

Rolling 5-Day Avg_FORUM =

//var __lastVisibleDay = MAX( DateTable[Date] )

var __lastVisibleDay = MAX( Q_SAP_LINES_BY_FLOW[PICKING_DATE] )

var __effectiveDates =

    TOPN(5,

        CALCULATETABLE(

            VALUES( Q_SAP_LINES_BY_FLOW[PICKING_DATE] ),

            DateTable[Date] <= __lastVisibleDay

//,

            // ALL is here just in case you've

            // forgotten to mark the DateTable

            // as the Date table in the model.

            //ALL( DateTable )

        ),

        Q_SAP_LINES_BY_FLOW[PICKING_DATE],

        DESC

    )

var __shouldCalculate =

    COUNTROWS( __effectiveDates ) = 5

return

    if( __shouldCalculate,

        AVERAGEX(

            TREATAS(

                __effectiveDates,

                DateTable[Date]

            ),

            // You have to add 0 to

            // [YourMeasure] if you

            // want to also treat

            // BLANK as 0 and include it

            // in the calculation of

            // the average. If you don't,

            // days where the measure is

            // BLANK are ignored.

            [M_Tot_line]

        )

    )

If I select all my picking date in my slicer, I've this result :

If I select a date range, the average is well calculated as I want :

BIG THX FOR YOUR HELP,

Fabrizio

[Fowmy]

@Falongi_82 

Great!

Accept the solutions so others can find this useful for them 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn