90 Days Average for Closed deals

DAX DAX Calculations
1

Hi Team,

Hope you are doing well.

I need some guidance to create this Dax. Scenario Below:

I have a line chart with closed deals by month. I want to add a 90 days average. There is a filter in page called ‘Is Before 6M’ , so that the page only shows 6 months data. With the average:

  • The avg in Sep 2020 would be Sum of closed deals in (Jul 2020 + Aug 2020 + Sep 2020)/3
  • The avg in Oct 2020 would be Sum of closed deals in (Aug 2020 + Sep 2020 + Oct 2020)/3
    and so on…

I’ve attached a sample pbix file for reference. Any help is much appreciated.

Many Thanks,
SuraajSample Deals.pbix (48.5 KB) Sample Deals Data.xlsx (12.0 KB)

2

Hello @Suraaj,

Thank You for posting your query onto the Forum.

Well you can use below provided formulas for the reference -

1. Calculation of Average as per 90 days -

90 Days Average = 
AVERAGEX(
    DATESINPERIOD(
        'Date'[Date] , 
        LASTDATE( 'Date'[Date] ) , 
        -90 ,
        DAY ) , 
    [Closed Deals] )

2. Calculation of Average as per 3 Months -

3 Months Average = 
AVERAGEX(
    DATESINPERIOD(
        'Date'[Date] , 
        LASTDATE( 'Date'[Date] ) , 
        -3 ,
        MONTH ) , 
    [Closed Deals] )

Results
Results1267×544 47.7 KB

I’m also attaching the working of the PBIX file for the reference.Also providing few of the links of the videos available onto this topic as well.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample Deals - Harsh.pbix (49.9 KB)

1 Like
3

Hi @Harsh,

Thank you for your response and details.

But what I was looking for was for each month the average of the total 3 months before. Please see attached image and excel.

Many Thanks90 Day Average.csv (1.1 KB)

90 days avg
90 days avg905×290 10.9 KB

4

Hello @Suraaj,

So create the formulas as provided below to achieve this type of result -

1. Calculation of Closed Deals Last Month -

Closed Deals LM = 
CALCULATE( [Closed Deals] , 
    DATEADD( 'Date'[Date] , -1 , MONTH ) )

2. Calculation of Closed Deals Last 2 Months -

Closed Deals L2M = 
CALCULATE( [Closed Deals] , 
    DATEADD( 'Date'[Date] , -2 , MONTH ) )

3. Calculation of Average of Last 3 Months -

Average of Last 3 Months = 
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Date'[MonthInCalendar] ) = "Jul 2020" , [Closed Deals] ,
    SELECTEDVALUE( 'Date'[MonthInCalendar] ) = "Aug 2020" , DIVIDE( [Closed Deals] + [Closed Deals LM] , 2 , 0 ) , 
    ISBLANK( [Closed Deals] ) , BLANK() , 
    DIVIDE( [Closed Deals] + [Closed Deals LM] + [Closed Deals L2M] , 3 , 0 ) )

4. Calculation to Achieve Correct Grand Totals of Average of Last 3 Months -

Average of Last 3 Months - Totals = 
SUMX(
    SUMMARIZE(
        'Date' , 
        'Date'[MonthInCalendar] , 
        "@Totals" , 
        [Average of Last 3 Months] ) , 
    [@Totals] )

Result

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample Deals - Hasrh v2.pbix (50.1 KB)

3 Likes
5

Hi @Suraaj,

Alternatively you could do something like this:

90 Day Avg = 
VAR ClosedDetails =
    CALCULATE( [Closed Deals],
        DATESINPERIOD( 'Date'[Date], EOMONTH( MAX( 'Date'[Date] ), 0), -3, MONTH )
    )
VAR NumOfMonths = 
    COUNTROWS(
        CALCULATETABLE( 
            FILTER( VALUES( 'Date'[MonthInCalendar] ),
                NOT( ISBLANK( [Closed Deals] ))
            ),  DATESINPERIOD( 'Date'[Date], EOMONTH( MAX( 'Date'[Date] ), 0), -3, MONTH )
        )
    )
VAR Result = DIVIDE( ClosedDetails, NumOfMonths )
RETURN

    Result

.

with this result

image

2 Likes
6

That’s brilliant. Works like magic :slight_smile:

Many Thanks for your help guys!