Formula Alternative

[Anonymous]

I've created a measure designed to provide the average of the previous quarter's retention rates:

This works as intended, however my dashboard is now really sluggish. I'm guessing from the amount of calculating going on in the background.  Anything I click on now needs 1-2 minutes to get itself together and the PBI file opens up slow as well if I close it. Is there any other way to get the same result without the formula slowing down my entire dashboard?

Just as an FYI, the dates come from a date table and the [retention] measure is coming from my fact table. My [retention] measure references my date table which is how the 2 are displaying in the same visual above.

[v-jiascu-msft]

Hi @Anonymous,

Did you solve it? Can you share the answer or mark the proper answer as a solution please?

Best Regards,
Dale

[Anonymous · Accepted solution]

@v-jiascu-msft Hi Dale, yes I've solved it. It's the long way I suppose but everything else I tried either returned blank values, or not the correct values. So what I did was create 3 measures that simply return the value at the end of each month that looks like this:

So the other 2 formulas would just say [Date Month] = 2 and [Date Month] = 3. This ensures that my values will remain exact. I then have this formula using all 3 measures:

It works exactly like I want it to, I just have to remember to come in and update the values for each measure when the next quarter arrives. So the measures next quarter will be changed to 7, 8 and 9. I've changed them to 4, 5 and 6 as of this morning to reflect quarter 2 results.

[v-jiascu-msft]

Hi @Anonymous,

Thank you so much for sharing the solution.

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

Maybe you can try the formula below. If you'd like a more precise formula, please provide a sample.

Last Quarter Retention =
CALCULATE (
    AVERAGEX ( SUMMARIZE ( 'Date', "R", [Retention] ), [R] ),
    PREVIOUSQUARTER ( 'Date'[Date] )
)

Best Regards,

Dale