Forum Discussion

mccollough's avatar
mccollough
Icon for Helper I rankHelper I
4 years ago
Solved

Calculate new clients over time

Let's say you want to trend the number of distinct client ID's over time from a table of invoices. I have an invoice table that looks like this Date Client ID Sale Amount 1/1/2021    100...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    This looks like a standard cumulative total pattern.

    Client Count = 
VAR CurrDate = MAX ( dimDate[Date] )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Table1[Client ID] ), dimDate[Date] <= CurrDate )
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

mccollough   you can use a measure like this

Measure = 
VAR _date =
    MAX ( 'Table'[Date] )
RETURN
    COUNTROWS (
        EXCEPT (
            VALUES ( 'Table'[Client ID] ),
            SUMMARIZE (
                FILTER ( ALL ( 'Table' ), 'Table'[Date] < _date ),
                'Table'[Client ID]
            )
        )
    )