Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |