Calculate Monthly Active Users for Further Calculation

[manish_tripathi]

I am trying to calculate DAU/MAU ratio for my dataset. I am working to get cumulative distinct count of uids on montly basis. My dataset consists dates and UserIDs active on that date. I am able to calculate DAU using DistinctCount, how ever i am unable to CALCULATE mau. In all rows for MAU for that month i want the total MAU number

https://github.com/manish-tripathi/Datasets/blob/main/Sample%20Data.xlsx 

My Input Data is as below

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

and i am expecting my output as 

	DAU	MAU
1-Jan	7	28
2-Jan	7	28
3-Jan	9	28
4-Jan	14	28

[amitchandak · Accepted solution]

@manish_tripathi , Prefer to use a date table with month year column

Try like

calculate(distinctcount(Table[User ID]) , filter(allselected(Table) , eomonth(Table[Date],0) = eomonth(max(Table[Date]),0) ) )

calculate(distinctcount(Table[User ID]) , filter(allselected(Date) , Date[Month Year] = Max( Date[Month Year] ) ) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[manish_tripathi]

Thank you so much for quick response