Unique Clients per Quarter

[pbrainard]

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 client IDs. I only want unique visitors if they weren't seen in a previous quarter. I've got the service date connected to a calendar table. I've got a slicer for the quarters.

[PaulDBrown]

I depends how the visual is structured (dimension fields or fields from fact table)

You will need semething alon the lines of:

Count =
VAR _PrevQuarter =
    CALCULATETABLE (
        VALUES ( Table[ClientID] ),
        DATEADD ( 'Calendar Table'[Date], -1, QUARTER )
    )
VAR _CurrentQuarter =
    VALUES ( Table[ClientID] )
VAR _Calc =
    COUNTROWS ( EXCEPT ( _CurrentQuarter, _PrevQuarter ) )
RETURN
    SUMX (
        SUMMARIZE (
            Table,
            Table[Pooled cities],
            'Calendar Table'[Quarter],
            "_Count", _Calc
        ),
        [_Count]
    )

Bear in mind this will just sum the values (ie the result will not be a DISTINCTCOUNT, but the sum of the distinctcounts, which I'm not sure makes any sense...)

[pbrainard]

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 · Accepted solution]

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

[PaulDBrown]

Try:

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

[pbrainard]

Looks to be working great for the quarters. Thank you!!! How can I get the total to aggregate across the quarters?