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've used the following Measure to prove that we can calculate a Daily over Monthly amount. (at least for the last month)
Solved! Go to Solution.
@jjgs ,
To be general, you can use calculate column instead of measure and add an ALLEXCEPT() function to calculate based on every day like pattern below:
Percentage of Monthly Active = CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( Query1, DAY ( Query1[Date] ) = EARLIER ( Query1[Date] ) ) ) / CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( Query1, Query1[timestamp] > TODAY () - 30 && Query1[Month] IN VALUES ( Date[Month] ) ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@jjgs ,
If the slicer is based on [Month] column in Datetable. You may modify your measure as pattern below:
Percentage of Monthly Active = DISTINCTCOUNT ( Query1[UniqueUserID] ) / CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( Query1, Query1[timestamp] > TODAY () - 30 && Query1[Month] IN VALUES ( Date[Month] ) ) )
If the slicer is based on [Month] column in same table. You may modify your measure as pattern below:
Percentage of Monthly Active = DISTINCTCOUNT ( Query1[UniqueUserID] ) / CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( ALLSELECTED ( Query1 ), Query1[timestamp] > TODAY () - 30 ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Jimmy,
The
TODAY () - 30
is really meant only for the original proof of concept where we were looking only at the last month.
I've modified to essentially use your suggestions, but when you drop it into a table or graph it still filters to each day to the same as the numerator. (Resulting in 100% for each day for a DAU/MAU calculation)
The intent was to create a fixed number that could represent the Total Monthly Uniques and could be the denominator in the daily calculation.
In other words DAU is the distinct count of the Daily users but counted per day,
and the MAU is the distinct count of the Month users but only resolved over the month selected.
So a table or graph can be constucted with each day shows:
Day 1, DAU for the day, MAU for the Month, DAU(for day 1)/MAU (for the month selected)
Day 2, DAU for the day, MAU for the Month, DAU(for day 2)/MAU (for the month selected)
@jjgs ,
To be general, you can use calculate column instead of measure and add an ALLEXCEPT() function to calculate based on every day like pattern below:
Percentage of Monthly Active = CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( Query1, DAY ( Query1[Date] ) = EARLIER ( Query1[Date] ) ) ) / CALCULATE ( DISTINCTCOUNT ( Query1[UniqueUserID] ), FILTER ( Query1, Query1[timestamp] > TODAY () - 30 && Query1[Month] IN VALUES ( Date[Month] ) ) )
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |