Count unique clientnr and subnr from start- to enddate

[Margreet]

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

ClientNr Count: = 
VAR _clienttable =
    CALCULATETABLE (
        SUMMARIZE ( Data , Data[ClientNr], Data[Startdate], Data[Enddate] ),
        REMOVEFILTERS ( 'Calendar' )
    )
VAR _quarterstart =
    MIN ( 'Calendar'[Date] )
VAR _quarterend =
    MAX ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                _clienttable,
                Data[Enddate] >= _quarterstart
                    && Data[Startdate] <= _quarterend
            ),
            Data[ClientNr]
        )
    )

SubNr Count: = 
VAR _SubNrtable =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[SubNr], Data[Startdate], Data[Enddate] ),
        REMOVEFILTERS ( 'Calendar' )
    )
VAR _quarterstart =
    MIN ( 'Calendar'[Date] )
VAR _quarterend =
    MAX ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                _SubNrtable,
                Data[Enddate] >= _quarterstart
                    && Data[Startdate] <= _quarterend
            ),
            Data[SubNr]
        )
    )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

ClientNr Count: = 
VAR _clienttable =
    CALCULATETABLE (
        SUMMARIZE ( Data , Data[ClientNr], Data[Startdate], Data[Enddate] ),
        REMOVEFILTERS ( 'Calendar' )
    )
VAR _quarterstart =
    MIN ( 'Calendar'[Date] )
VAR _quarterend =
    MAX ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                _clienttable,
                Data[Enddate] >= _quarterstart
                    && Data[Startdate] <= _quarterend
            ),
            Data[ClientNr]
        )
    )

SubNr Count: = 
VAR _SubNrtable =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[SubNr], Data[Startdate], Data[Enddate] ),
        REMOVEFILTERS ( 'Calendar' )
    )
VAR _quarterstart =
    MIN ( 'Calendar'[Date] )
VAR _quarterend =
    MAX ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                _SubNrtable,
                Data[Enddate] >= _quarterstart
                    && Data[Startdate] <= _quarterend
            ),
            Data[SubNr]
        )
    )

[Margreet · Accepted solution]

Super thanks!! Very helpfull this. GreatingsMargreet