How to get distinct count of Existing client for current and Previous Year

[Naksh]

Hello,
I am currently facing a challenge in my Power BI project and would greatly appreciate your assistance in resolving it.

Problem Statement: I am working on a project where I need to calculate the distinct count of existing client IDs for the Current Year who joined in the selected year from a slicer. Additionally, I also need to calculate the distinct count of existing clients for the previous year and display these counts in a table format with the variance of it.

Attached to this message is the PBIX file containing sample data. I would be immensely grateful if someone could take a look at it and provide guidance on how to solve this issue.
I have also attached how wanted to show that data in table format. FYI it is just a sample value not related to a sample data file.

Sample Data PBIX 

Thank you!

[MNedix · Accepted solution]

The sample data does not stretch too much but I believe the two measures will become:

CY = DISTINCTCOUNT('Sample Data'[mspClientID])

PY = CALCULATE([CY],DATEADD('Sample Data'[startDateTime],-1,YEAR))

With these and the date filter set as "Between" you can compare any period you want with the previous 1 year.

If this solved your problem please mark is as the solution so others can see it.

[v-jiewu-msft · Accepted solution]

Hi @Naksh ,

I can’t open the link file.

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the measure to calculate CY.

Existing client cy = 
CALCULATE(
    DISTINCTCOUNT('Table'[client IDs]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)

3.Create the measure to calculate PY.

Existing client PY = 
CALCULATE(
    DISTINCTCOUNT('Table'[client IDs]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = SELECTEDVALUE('Table'[Year]) - 1
    )
)

4.Create the measure to calculate Variance.

Variance = [Existing client cy] - [Existing client PY]

5.Drag the year into the slicer visual.

6.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MNedix]

Hi @Naksh,

This should be quite straight forward - see below measures:

This Year = CALCULATE(DISTINCTCOUNT('Sample Data'[mspClientID]),DATESYTD('Sample Data'[startDateTime]))

Last Year = CALCULATE(DISTINCTCOUNT('Sample Data'[mspClientID]),PARALLELPERIOD('Sample Data'[startDateTime],-1,YEAR))

Variance = [This Year]-[Last Year]

If this solved your problem please mark is as the solution so others can see it.

[Naksh]

Thank you For your reply to this question,
However, the count for existing clients should be determined if the client already existed in the past years.
For example:- If the selected year is 2022 then from all the past years it will take the distinct count of client id which is the same in all those years. 
Same for the PY Existing client count, for that the selected year is 2022 but it will take count of clients by considering the CY - 1 i.e. 2021 - 1= 2020

[MNedix · Accepted solution]

The sample data does not stretch too much but I believe the two measures will become:

CY = DISTINCTCOUNT('Sample Data'[mspClientID])

PY = CALCULATE([CY],DATEADD('Sample Data'[startDateTime],-1,YEAR))

With these and the date filter set as "Between" you can compare any period you want with the previous 1 year.

If this solved your problem please mark is as the solution so others can see it.