Running Avg of Rates by group

[csudude]

Hello,

I wasn't sure if I should post this in the DAX forum or in the PBI forum.  I'm running into an issue calculating a running average that is partitioned by 2 fields.       

The measure I am trying to calculate is AvgChurnRate as seen on the far right in the image below: 


AvgChurnRate is a running average of the ChurnRate measure.  However, the AvgChurnRate should only be the running average of ChurnRate for rows with the same AquiredMonth and AttriterType.  AquiredMonth is a column and AttriterType is a measure.  

In SQL, the code would look something like this:
,AVG(ChurnRate) OVER (PARTITION BY AcquiredMonth, AttriterType ORDER BY ChurnMonth ASC) AvgChurnRate 

This is my best DAX effort:

AvgChurnRate =

VAR currAquiredMonth =

    MAX ( Digital_sp_Rpt_CLV_Data[AquiredMonth] )

VAR currChurnMonth =

   MAX ( Digital_sp_Rpt_CLV_Data[ChurnMonth] )

VAR currAttritType =

   MAX (Digital_sp_Rpt_CLV_Data[AttriterTypeColumn])

RETURN

AVERAGEX (

        FILTER(

              SUMMARIZE(Digital_sp_Rpt_CLV_Data,

                                  Digital_sp_Rpt_CLV_Data[AquiredMonth],

                                  Digital_sp_Rpt_CLV_Data[ChurnMonth],

                                  Digital_sp_Rpt_CLV_Data[AttriterTypeColumn],

                                  "Churn",[ChurnRate]),

               Digital_sp_Rpt_CLV_Data[AquiredMonth] = currAquiredMonth && Digital_sp_Rpt_CLV_Data[ChurnMonth] <= currChurnMonth && Digital_sp_Rpt_CLV_Data[AttriterTypeColumn] = currAttritType),

[Churn]

)

Obviously the above code doesn't work.  I would be very appreciative of any help I can get.  

Thank you!

[amitchandak]

Earlier should help with this. try below and do any modification needed.

         CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]), 
          FILTER(ALL(Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= earlier(Digital_sp_Rpt_CLV_Data[ChurnMonth]) &&
                                               Digital_sp_Rpt_CLV_Data[AcquiredMonth] = earlier(Digital_sp_Rpt_CLV_Data[AcquiredMonth]) &&
                                               Digital_sp_Rpt_CLV_Data[AttriterType] = earlier(Digital_sp_Rpt_CLV_Data[AttriterType])))

OR
CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]), 
          FILTER((Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= earlier(Digital_sp_Rpt_CLV_Data[ChurnMonth]) &&
                                               Digital_sp_Rpt_CLV_Data[AcquiredMonth] = earlier(Digital_sp_Rpt_CLV_Data[AcquiredMonth]) &&
                                               Digital_sp_Rpt_CLV_Data[AttriterType] = earlier(Digital_sp_Rpt_CLV_Data[AttriterType])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[HotChilli]

For faster answers, please post data (not a picture of the data)

Here's a measure (I'm not entirely sure it will work since attritertype is a measure but see how you get on)

MeasureTest = VAR _AMonth = MAX(Digital_sp_Rpt_CLV_Data[AcquiredMonth])
            VAR _AType = MAX(Digital_sp_Rpt_CLV_Data[AttriterType])
            VAR _CMonth = MAX(Digital_sp_Rpt_CLV_Data[ChurnMonth])
RETURN
            CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]), 
          FILTER(ALL(Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= _CMonth &&
                                               Digital_sp_Rpt_CLV_Data[AcquiredMonth] = _AMonth &&
                                               Digital_sp_Rpt_CLV_Data[AttriterType] = _AType))