Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I currently have three measures, each used by the previous to achieve an outcome. I am certain this could be done in a more efficient way and am looking for help to get there.
Here is some made-up data that includes sales, product changes and terminations.
The goal is to see what the membership base "looks like" at any point in time dynamically, i.e. it should only see the most recent row for a given member key. I also need to be able to see the cumulative count at any point in time.
Here are the measures which are giving me the desired result, but the performance is very slow on real data.
1 - Current Row =
VAR _Current = SELECTEDVALUE('Members'[MemberKey])
VAR _Date = MAXX(
FILTER(
ALLSELECTED('Members'),
'Members'[MemberKey] = _Current
),
RELATED('Date'[Date])
)
RETURN
CALCULATE(
COUNTROWS('Members'),
'Members'[MemberKey] = _Current,
'Date'[Date] = _Date
)
2 - Member Count At Date =
CALCULATE(
COUNTROWS('Members'),
FILTER('Members', [1 - Current Row] = 1),
'Members'[MemberStatus] = "active",
'Date'[Date] <= MAX('Date'[Date])
)
3 - Cumulative Member Count At Date =
CALCULATE(
[2 - Member Count At Date],
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
I have tried to upload the minimal example I created but it seems I do not have permission to do that yet...
Thanks.
You can try the below. You will need to have a column which has unique values for each row. If your model doesn't have one you can use Power Query to add an index column. When you have the column, you need to go into modelling view and mark that column as the key for the table.
Active members at date =
VAR ReferenceDate =
MAX ( 'Date'[Date] )
VAR SummaryTable =
CALCULATETABLE (
INDEX (
1,
Members,
ORDERBY ( Members[Date], DESC ),
PARTITIONBY ( Members[Member Key] )
),
'Date'[Date] <= ReferenceDate
)
VAR Result =
COUNTROWS ( FILTER ( SummaryTable, [Member Status] = "active" ) )
RETURN
Result
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |