Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |