Forum Discussion

manish_tripathi's avatar
manish_tripathi
Frequent Visitor
3 years ago
Solved

Calculate Monthly Active Users for Further Calculation

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

DateUser ID
1-Jan123
1-Jan234
1-Jan123
1-Jan234
1-Jan456
1-Jan564
1-Jan5644
1-Jan235
1-Jan2354
2-Jan235
2-Jan2354
2-Jan125
2-Jan1264
2-Jan564
2-Jan897
2-Jan987
3-Jan123
3-Jan321
3-Jan1265
3-Jan1256
3-Jan1553
3-Jan654
3-Jan9875
3-Jan5647
3-Jan6542
4-Jan321
4-Jan124
4-Jan1256
4-Jan325
4-Jan987
4-Jan568
4-Jan2589
4-Jan6547
4-Jan3321
4-Jan458
4-Jan123
4-Jan1264
4-Jan821
4-Jan542
4-Jan123
4-Jan1264
4-Jan821
4-Jan542

 

and i am expecting my output as 

 

 DAUMAU
1-Jan728
2-Jan728
3-Jan928
4-Jan1428
General Comment
Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    3 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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.