Forum Discussion
AVERAGE
dedelman_clng I'm not sure how I would do that. Here are the pieces to my retention formula:
So my numerator is [Active]-[Added]. I get what you are saying, I'm just a bit novice in my PBI skills to put together what you've said.
Community ChampionAnonymous -
I think you are going to want to essentially recreate the 3 measures used for retention, but with the "last quarter" filter you were using in AVERAGEX. So create
[LQ Added]
[LQ Active]
[LQ Starting]
and then your [LQ Retention] is essentially the same formula
( [LQ Added] - [LQ Active] ) / [LQ Starting]
dedelman_clng I see. I will give it a try and let you know the outcome! Thank you for the advice.
dedelman_clng This puts me back to a place I was at previously:
It is only returning the last value of the last month of quarter 1. It is not taking february or january into the formula. This is what each individual formula looks like:
- dedelman_clng
Anonymous - because you have competing filters in [Active] and [LQ Active], you will likely have to redo the calculation part of [Active] (DISTINCTCOUNT) in [LQ Active] (rather than referring to the other measure).
Also, you may need to "clear" the filters from DATE when calculating the LQ measures (since you're showing the data in table, the measure will inherit the implicit filter from the table
...FILTER(ALL('Date'), 'Date[Year]'=2018...)
Time intelligence is always tricky in DAX/PowerBI - I have found that often you have to resort to building calculations for time comparisons twice.
dedelman_clng I think I see. So my LQ Active would look something like:
LQ Active = CALCULATE(DISTINCTCOUNT(M3[co]),FILTER(M3,M3[startDate]<=MAX('Date'[Date])&&M3[enddate]>=MAX('Date'[Date])),FILTER(ALL('Date'),'Date'[Year]=2018&&'Date'[Quarter]="Qtr 1")))