locked
Previous Quarter Average Calculation RRS feed

  • Question

  • I have created 3 measures to calculate a trailing 12 month retention rate:

    The retention formula = ([Active] - [Added])/[Starting]*100

    Here is what each measure looks like:

    [Active] = CALCULATE(DISTINCTCOUNT(Co),FILTER(M3,M3[StartDate]<=MAX('DATE'[Date])&&M3[EndDate]>=MAX('DATE'[DATE])))

    This returns the amount of active clients today

    [Added] = CALCULATE(DISTINCTCOUNT(Co),FILTER(M3,M3[StartDate]>=MAX('DATE'[Date])-365&&M3[StartDate]<=MAX('DATE'[Date])))

    This returns the amount of clients added within the past year

    [Starting] = CALCULATE(DISTINCTCOUNT(Co),FILTER(M3,M3[StartDate]<=MAX('DATE'[Date])-365&&M3[EndDate]>=MAX('DATE'[DATE])-365))

    This returns what the amount of active clients from 1 year ago

    So then using my Date table, I created a [Month End] column so we can see at the end of each month where the trailing 12 month average sits.

    What I'm trying to do is figure out how to get the average of these values from the previous quarter. So for quarter 1, the values look like this:

    Month End: 1/31/2018 Retention: 87.08

    Month End: 2/28/2018 Retention: 87.59

    Month End: 3/31/2018 Retention: 87.46

    I attempted to create a measure to calculate the average of these 3 values but it is only returning the value for March and not an average of the whole quarter which should be returning 87.38.

    The formula I created looks like this:

    Last Quarter Retention = CALCULATE([Retention],FILTER('Date','Date'[Year]=2018&&'Date'[Quarter]="Qtr 1")) which is returning 87.46.

    I've tried using the AVERAGEX function, but for some reason it changes the values before spitting out the average, so it tends to be off by a slight margin in the final result. It also causes my entire dashboard to run sluggishly afterwards. I feel like there has to be a simple way to add these 3 values and divide by 3? I wish I could just stick it inside the AVERAGE function but it only accepts columns whereas everything I'm using is a measure.


    Friday, June 22, 2018 6:40 PM

Answers

  • Hi Marc,

    Thanks for your question.

    It is pretty hard to answer this without sample data. To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.

    If I understand you correctly, you can try to refer to below similar thread:
    https://community.powerbi.com/t5/Desktop/Calculate-Quarterly-Average-of-measure/td-p/252829


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 25, 2018 5:44 AM

All replies

  • Here is a screenshot from what I've asked in another forum. Been asking there for days and never received a working solution so I figured I'd try here:

    Friday, June 22, 2018 6:09 PM
  • Hi Marc,

    Thanks for your question.

    It is pretty hard to answer this without sample data. To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.

    If I understand you correctly, you can try to refer to below similar thread:
    https://community.powerbi.com/t5/Desktop/Calculate-Quarterly-Average-of-measure/td-p/252829


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 25, 2018 5:44 AM