Cumulative DISTINCTCOUNT with time intelligence

[my76]

I am having trouble with DAX and need some help! Here is what I am trying to do:

I want to report on DISTINCTCOUNT of TenantID cumulatively by Week, Month and Quarter. Below is what I have attempted for Month but it is only returning the distinct count per day and not cumulatively for the Month. I am creating it as a Measure in the table 'MAC over QuAC' which has a relationship with the 'pageviews' table using timestamp/date columns.

Cumulative Tenant Count by Month =

CALCULATE(

CALCULATE(DISTINCTCOUNT(pageviews[TenantID]),

FILTER(pageviews, pageviews[timestampformatted] <= pageviews[timestampformatted]),

DATESMTD('MAC over QuAC'[Date])))

Once I figure this out I also need to work out how to report on a rolling 30d and 90d basis where the daily value is recalculated each day.

Thanks in advance!
Matt

[v-juanli-msft]

Hi @my76 

You could get the cumulatively DISTINCTCOUNT of TenantID by Week, Month and Quarter..

First create a date table with date, year, Week, Month and Quarter.

Then create a relationship between this date table and your main table.

then create a measure to get DISTINCTCOUNT of TenantID,

Measure = DISTINCTCOUNT(Sheet2[id])

after add year, Quarter, Month, Week, in the row field of the matrix, the measure can change based on expanding to each level.

you can create a measure to get the cumulatively DISTINCTCOUNT of TenantID by Week, Month and Quarter.

Measure 2 = CALCULATE(DISTINCTCOUNT(Sheet2[id]),FILTER(ALLSELECTED(Sheet2),Sheet2[timestamp]<=MAX(Sheet2[timestamp])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[my76]

@v-juanli-msft  really helpful example but we are not quite there yet...

Whilst measure 2 gives a DISTINCTCOUNT, it does so cumulatively over the whole reporting period. i.e. August is a DISTINCOUNT from the earliest date (March) to the August dated rows. The resulting lin chart shows an increasing trend over the full period; when each Monthly period actually has fluctuating DISTINTCOUNTS e.g. August has 3 DISTINCTOUNT values, but Measure 2 reports it as 4. I can get the monthly values using Measure 1 (DISTINTCOUNT of ID).

I need the values to use in a formula = ID count by Month / ID count by Quarter

Maybe creating a separate table with the Distintcounts over Day/Week/Month/Quarter/Year periods would be the answer?

Once we have the above working I need it to show the DISTINTCOUNT for Weekday to Date, Week to Date, Month to Date, Quarter to Date in the context of the full reporting period. 

Also, how can I calculate the DISTINCTCOUNT for the last 90 days to get a rolling 90 day value each day?

[v-juanli-msft]

Hi @my76 

Create a date table having relationship with my main table.

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "quarter", SWITCH (
        TRUE (),
        MONTH ( [Date] ) IN { 1, 2, 3 }, "q1",
        MONTH ( [Date] ) IN { 4, 5, 6 }, "q2",
        MONTH ( [Date] ) IN { 7, 8, 9 }, "q3",
        MONTH ( [Date] ) IN { 10, 11, 12 }, "q4"
    ),
    "month", MONTH ( [Date] ),
    "week", WEEKNUM ( [Date], 2 ),
    "day", DAY ( [Date] )
)

1.  creating a separate table with the Distintcounts over Day/Week/Month/Quarter/Year periods

Create a measure, add these columns to a matrix visual

Distintcounts over periods = DISTINCTCOUNT(Sheet2[id])

You can expand the levels

2. DISTINTCOUNT for Weekday to Date, Week to Date, Month to Date, Quarter to Date in the context of the full reporting period

quarter to date = TOTALQTD([Distintcounts over periods],'date'[Date])

month to date = TOTALMTD([Distintcounts over periods],'date'[Date])

week to date = CALCULATE([Distintcounts over periods],FILTER(ALLSELECTED('date'),'date'[week]<=MAX('date'[week])&&'date'[year]=MAX('date'[year])))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[my76]

**Bump** 

Any more ideas? 

Thanks

Matt

[lc_finance]

Hi @my76 ,

Have you tried using TOTALMTD?

https://docs.microsoft.com/en-us/dax/totalmtd-function-dax

This should give you exactly what you are looking for.

If you still have issues, can you share a sample Power BI file (via OneDrive, Google Drive or similar)?

Regards

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com