Calculating a sum of unique over a sliced date range

[jjgs]

I've used the following Measure to prove that we can calculate a Daily over Monthly amount. (at least for the last month)

Percentage of Monthly Active = DISTINCTCOUNT(Query1[UniqueUserID])/CALCULATE(DISTINCTCOUNT(Query1[UniqueUserID]),Query1[timestamp]>TODAY()-30)

Now looking to switch that to work with a Month slicer where the denominator is the distinctcount of the UniqueUserIDs over the period sliced, but can't seem to get it.

[v-yuta-msft]

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

[jjgs]

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)

[v-yuta-msft · Accepted 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.