Cumulative DISTINCTCOUNT with time intelligence

Question

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

lc_finance · Answer

Hi&nbsp;my76&nbsp;,&nbsp;&nbsp;Have you tried using TOTALMTD?https://docs.microsoft.com/en-us/dax/totalmtd-function-daxThis should give you exactly what you are looking for.&nbsp;If you still have issues, can you share a sample Power BI file (via OneDrive, Google Drive or similar)?&nbsp;Regards&nbsp;LCInterested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

v-juanli-msft · Answer

Hi&nbsp;my76&nbsp;
You could get the&nbsp;cumulatively&nbsp;DISTINCTCOUNT of TenantID by&nbsp;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&nbsp;DISTINCTCOUNT of TenantID, 
Measure = DISTINCTCOUNT(Sheet2[id])

after add&nbsp;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&nbsp;cumulatively&nbsp;DISTINCTCOUNT of TenantID by&nbsp;Week, Month and Quarter.
Measure 2 = CALCULATE(DISTINCTCOUNT(Sheet2[id]),FILTER(ALLSELECTED(Sheet2),Sheet2[timestamp]&lt;=MAX(Sheet2[timestamp])))

Best RegardsMaggie
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

my76 · Answer

v-juanli-msft&nbsp; really helpful example but we are not quite there yet...&nbsp;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).&nbsp;I need the values to use in a formula = ID count by Month / ID count by Quarter&nbsp;Maybe creating a separate table with the Distintcounts over Day/Week/Month/Quarter/Year periods would be the answer?&nbsp;Once we have the above working&nbsp;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.&nbsp;&nbsp;Also, how can I calculate the DISTINCTCOUNT for the last 90 days to get a rolling 90 day value each day?

my76 · Answer

**Bump**&nbsp;&nbsp;Any more ideas?&nbsp;&nbsp;ThanksMatt

v-juanli-msft · Answer

Hi&nbsp;my76&nbsp;
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] )
)

&nbsp;
1. &nbsp;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

&nbsp;
2.&nbsp;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]&lt;=MAX('date'[week])&amp;&amp;'date'[year]=MAX('date'[year])))
Best RegardsMaggieCommunity Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Cumulative DISTINCTCOUNT with time intelligence

5 Replies

Related content

Cumulative Total

Creating Cumulative Graphs

Help with Date intelligence

DISTINCTCOUNT with filters

Year to date cumulative