Calculate cumulative distinct count of member by different stages

[PowerBI_User_XV]

Hi community, I have three table with members with different status and the date when they reach that status, each table represent a status. I have a Calendar table related to each tabla date column. Each table has one row by member. I want to calculate cumulative distinct count of members by months, for three combinations: those on Stage1, those on Stage1 and Stage2, those on Stage1 and Stage2 and Stage3. This is my sample table:

Stage1

Id	Stage1_Date
mbr1	01/20/2025
mbr2	01/15/2025
mbr3	02/04/2025
mbr4	03/25/2025
mbr5	03/16/2025
mbr6	05/20/2025

Stage2

Id	Stage2_Date
mbr1	03/20/2025
mbr2	01/18/2025
mbr3	02/15/2025
mbr4	04/25/2025
mbr5	05/20/2025
mbr6	05/26/2025

And a similar table for Stage3.

The expected output is:

Stage	Jan	Feb	Mar	Apr	May
Stage1	2	3	5	5	6
Stage1 + Stage2	1	2	3	4	6
Stage1 + Stage2 +Stage 3	x	x	x	x	x

I have achieved the first output line, that's a ease one. I have try different dax formulas that seem work, however when I validate the numbers are not right.

This is one of the formulas I have tried for the last output row:

Stage1 + Stage2 +Stage 3 :=

VAR CurrentMonthEndDate = MAX ( 'Calendar'[Date] )

VAR Stage1_Table = SELECTCOLUMNS(ALL(Stage1), "MBR_ID",[Id])

VAR Stage2_Table = SELECTCOLUMNS(ALL(Stage2), "MBR_ID",[Id])

RETURN

CALCULATE (

DISTINCTCOUNT ( Stage3[Id] ),

FILTER (

ALL ( 'Calendar' ),

'Calendar'[Date] <= CurrentMonthEndDate

),

Stage3[Id] IN Stage1_Table &&

Stage3[Id] IN Stage2_Table 

)

I appreciate any help!

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[v-menakakota]

Hi @PowerBI_User_XV   ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @Ashish_Mathur , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team.

[v-bmanikante]

Hello @PowerBI_User_XV  ,

I am also part of CST Team and we’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Regards,

B Manikanteswara Reddy

[v-menakakota]

Hi @msprog   ,
Thanks for reaching out to the Microsoft fabric community forum. 

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team.

[GeraldGEmerick]

@PowerBI_User_XV Seems like it would be easier to do it this way:

Distinct Count =
VAR _Table = UNION( 'Stage1', 'Stage2', 'Stage3' )
VAR _Return = DISTINCTCOUNT( SELECTCOLUMNS( _Table, "Id", [Id] ) )
RETURN _Return

[PowerBI_User_XV]

Thank you for your answer, but what about the dates? The idea is, for the output second row, distint count of members that had a January date in both table Stage1 and Stage2, same for Feb ... an so on. For the third output row, I need how many had Stage1 in January, Stage2 in Jabuary and Stage3 in January ... and so on for each month.