CODEWITHSUNDEEP
powerbidaxrolling-average
Rose
Rose

Reputation: 3

DAX - PowerBI - Rolling 3 Month Average Measure for Clustered Bar Line Chart

I have tried every solution from similar questions and none have worked thus far. I have a table of individual IDs, their Date, and an Action Indicator (0 or 1). I created a clustered bar and line chart that has the total count of IDs for each month as the bar, and their average action rate as a line. I want a measure that will create a trailing 3-month average line for the action rates. The closest I have gotten so far is:

Trailing 3 Month Action % = 
VAR CurrentDate = MAX('Table'[Date])
VAR ThreeMonthsAgo = EDATE(CurrentDate, -3)
VAR ActionsInLast3Months = 
    CALCULATE(
        SUM('Table'[Action_ind]),
        'Table'[Date] > ThreeMonthsAgo && 'Table'[Date] <= CurrentDate
    )
VAR TotalIDsInLast3Months = 
    CALCULATE(
        DISTINCTCOUNT('Table'[ID]),
        'Table'[Date] > ThreeMonthsAgo && 'Table'[Date] <= CurrentDate
    )
RETURN
    DIVIDE(ActionsInLast3Months, TotalIDsInLast3Months, 0)

However, the calculation is incorrect. These are examples values this measure outputs into the chart (that don't add up).

| Month | ID  |  Total Actions  | Action Rate | *Dax calculated | *What I expected |
| Jan   | 321 |  182            |    56.70%   |    
| Feb   | 240 |  147            |    62.25%   |    
| Mar   | 350 |  195            |    55.71%   |    59.27%       |  64.61%
| Apr   | 351 |  209            |    59.54%   |    61.83%       |  58.55%
| May   | 280 |  178            |    63.57%   |    64.73%       |  59.33%

I assumed March would be (195+147+182)/(350+240+321) = 64.61%

April would be (209+195+147)/(351+350+240) = 58.55%

May would be (178+209+195)/(280+351+350) = 59.33%

I have no idea how DAX is getting to their result, and how to get it to match my expectations. Any help would be greatly appreciated!

Upvotes: 0

Views: 53

Answers (1)

Nick A
Nick A

Reputation: 1124

Try this measure:

Trailing 3 Month Action % = 
VAR thisDate = MAX('Table'[Date])
VAR thisDateStart = EOMONTH(thisDate,-3)+1
VAR thisDateEnd = EOMONTH(thisDate,0)

VAR countID = CALCULATE(COUNT('Table'[ID])
        , FILTER(ALL('Table')
            , 'Table'[Date] >= thisDateStart
            && 'Table'[Date] <= thisDateEnd
        )
)

VAR sumActions = CALCULATE(SUM('Table'[Action_ind])
        , FILTER(ALL('Table')
            , 'Table'[Date] >= thisDateStart
            && 'Table'[Date] <= thisDateEnd
        )
)

RETURN DIVIDE(sumActions, countID)

In this measure, we are getting the start date of the month, 3 months prior. As well as the end date of the current month to ensure we collect all the data before dividing it.

Upvotes: 1

Related Questions