Forum Discussion

my76's avatar
my76
Frequent Visitor
6 years ago

Cumulative DISTINCTCOUNT with time intelligence

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 attemp...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    my76
    Frequent Visitor
    6 years ago

    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?

    • my76's avatar
      my76
      Frequent Visitor
      6 years ago

      **Bump** 

       

      Any more ideas? 

       

      Thanks

      Matt

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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.