cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

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
1 ACCEPTED SOLUTION
Super User

@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 2
Super User

@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.

Frequent Visitor

Thank you so much for quick response

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors