Active users on a given date in a Month

[manish_tripathi]

I am working to get cumulative distinct count of uids on daily basis. My dataset consists dates and UserIDs active on that date. Example : Say there are 2 uids (235,2354) appeared on date 2022-01-01 and they also appeared on next day with new uid 125 (235,2354,125) on 2022-01-02 At this point i want store cumulative count to be 3 not 5 as (user id 235 and 2354 already appeared on past day ).

My Sample Data looks like as follows

Date	User ID
1-Jan	123
1-Jan	234
1-Jan	123
1-Jan	234
1-Jan	456
1-Jan	564
1-Jan	5644
1-Jan	235
1-Jan	2354
2-Jan	235
2-Jan	2354
2-Jan	125
2-Jan	1264
2-Jan	564
2-Jan	897
2-Jan	987
3-Jan	123
3-Jan	321
3-Jan	1265
3-Jan	1256
3-Jan	1553
3-Jan	654
3-Jan	9875
3-Jan	5647
3-Jan	6542
4-Jan	321
4-Jan	124
4-Jan	1256
4-Jan	325
4-Jan	987
4-Jan	568
4-Jan	2589
4-Jan	6547
4-Jan	3321
4-Jan	458
4-Jan	123
4-Jan	1264
4-Jan	821
4-Jan	542
4-Jan	123
4-Jan	1264
4-Jan	821
4-Jan	542

I am expecting following output

	DAU	Daily_Cumulative_Count
1-Jan	7	7
2-Jan	7	11
3-Jan	9	19
4-Jan	14	28

[m_alireza · Accepted solution]

Hi @manish_tripathi ,

Try this measure

Daily_Cumulative_Count = CALCULATE(DISTINCTCOUNT('Table'[User ID]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[manish_tripathi]

Thank you by replacing all with allselected it worked like a charm

[Ashish_Mathur]

You are welcome.

[m_alireza · Accepted solution]

Hi @manish_tripathi ,

Try this measure

Daily_Cumulative_Count = CALCULATE(DISTINCTCOUNT('Table'[User ID]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

[manish_tripathi]

Thank you now i have two ways to solve the problem. Thank you for your help