Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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 , 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.
Thank you so much for quick response
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |