Forum Discussion

pbrainard's avatar
pbrainard
Icon for Helper III rankHelper III
4 years ago
Solved

Unique Clients per Quarter

I have a table of clients seen, broken down by quarter. As you can see the Total is unique clients but the quarters reflect unique clients in those quarters. The numbers are a distinct count of clien...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    You can use this code:

     

    Count =
VAR _PrevQuarter =
    CALCULATETABLE (
        VALUES ( Table[ClientID] ),
        DATEADD ( 'Calendar Table'[Date], -1, QUARTER )
    )
VAR _CurrentQuarter =
    VALUES ( Table[ClientID] )
RETURN
    IF (
        ISINSCOPE ( 'Calendar Table'[Quarter] ),
        COUNTROWS ( EXCEPT ( _CurrentQuarter, _PrevQuarter ) ),
        DISTINCTCOUNT ( Table[ClientID] )
    )

     

    This will provide the difference in quarters for the Quarter filter context (including the totals) and the total distincount per pooled cities on the row level and grand total. It might be worth providing an explanation to avoid false assumptions, or simply leave out the row totals altogether

pbrainard's avatar
pbrainard
Icon for Helper III rankHelper III
4 years ago

Yeah, you're right, doesn't make sense. Is there a way to incorporatethe year into the original code you shared, so it only counts IDs in the year selected in another slicer?

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

You can use this code:

 

Count =
VAR _PrevQuarter =
    CALCULATETABLE (
        VALUES ( Table[ClientID] ),
        DATEADD ( 'Calendar Table'[Date], -1, QUARTER )
    )
VAR _CurrentQuarter =
    VALUES ( Table[ClientID] )
RETURN
    IF (
        ISINSCOPE ( 'Calendar Table'[Quarter] ),
        COUNTROWS ( EXCEPT ( _CurrentQuarter, _PrevQuarter ) ),
        DISTINCTCOUNT ( Table[ClientID] )
    )

 

This will provide the difference in quarters for the Quarter filter context (including the totals) and the total distincount per pooled cities on the row level and grand total. It might be worth providing an explanation to avoid false assumptions, or simply leave out the row totals altogether