Distinctcount of ClientID's which resets at the start of every year

[Anonymous]

Hi,

I would like to create a distinctcount of clientID's where every year the clientID has to start at zero.
So basically, a new or recurring client should be (distinct)counted from the start of every year, but should be distinctcounted only once that year for the month till end of December.

In the result table adding categories should work as follows: if for example a productcategory is added in the table, the clientID should be distinctcounted for that productcategory, which could for example result in clientID 1944 in January 2018 for the automotive productcategory and for example May 2018 for the aviation automotive productcategory.

Currently I have the following table for which I used the CALCULATE(DISTINCTCCOUNT(CLIENTID)) measure:

 For the solution to work, ClientID 1944 should not be counted in February 2018 here.

(NB Src_ClientNr = ClientID)

Next I tried CALCULATE(DISTINCTCCOUNT(CLIENTID), ALL('datetable[month])) formula which didn't work.

Also tried a RANKX formula, but RANKX (if I understand correctly) gives a vertical oriented rank, and in this table it should be a horizontal rank.

How would you advice to solve it? Thank you in advance for your help/feedback.

[Anonymous]

I almost found the solution with the help of the following post and answer of Ashish_Mathur:

https://community.powerbi.com/t5/Desktop/DAX-Count-Only-the-first-occurence-of-a-value/td-p/411034

I used the following Measure:
Measure = CALCULATE(DISTINCTCOUNT('FactTable'[clientID]),FILTER(SUMMARIZE(VALUES('FactTable'[clientID]),[clientID],"ABCD",COUNTROWS('FactTable')),[ABCD]=1))

and it gave me the following result:

This is almost correct, but the 1 of clientID should be in Jan 2018 and not in Feb 2018.

For additional info on the side: If I change the last part of the formula to: [ABCD]=2) it give me the following result:

I understand that only clientID 1944 show up, because that is the only client with two distinctcount in the original table. I do not understand why the 1 shows up in January and not in February, it is almost like the formule starts in December and counts backwards.

What part of the formula do I need to change to get to the required full result?

[Ashish_Mathur]

Hi,

I do not understand your question.  Please share a small dataset, describe your question and show the expected result.  Please ensure that in the file that you share, the column headings are in English.

[Anonymous]

The input table is:

The required end result is a distinctcount for every unique clientID for every year:

In this example, for clientID 1944 there should only show up a 1 in January 2018 and in January 2019 (and NOT in February 2018 and NOT in February 2019). With the following measure, I came very close (see my post in this thread): Measure = CALCULATE(DISTINCTCOUNT('FactTable'[clientID]),FILTER(SUMMARIZE(VALUES('FactTable'[clientID]),[clientID],"ABCD",COUNTROWS('FactTable')),[ABCD]=1))
This last formula gave me a 1 in February 2018 and a blank in January 2018 but it should be just the other way around (NB this last formula I tested only for year 2018)

[V-lianl-msft]

Try to create a measure like this:

Measure = var min_of_year = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Date].[Year],'Table'[Client ID]))
return CALCULATE(DISTINCTCOUNT('Table'[Client ID]),FILTER('Table','Table'[Date]=min_of_year))

[Anonymous]

Can you please upload your PowerBI document? Want to learn this option as well and if it works will flag it as a solution as well. Thank you in advance.

[V-lianl-msft]

Hi @Anonymous ,

Please check the attachment

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

[Anonymous]

Thank you for your feedback. I am almost there but I am probably still missing something. I imported the simplified Excel table in a new PowerBI document to try to get it to work there. I am using the following formula:

Measure =
VAR MINYEAR = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Date], 'Table'[Year], 'Table'[clientID]))
RETURN
CALCULATE(DISTINCTCOUNT('Table'[clientID]),FILTER('Table','Table'[Date]=MINYEAR))

In the X-axis I use YearMonth, but I calculated that directly from the Table with a [Year][Month] formula.

Somehow it only gives me the (right) result for year 2018, year 2019 is not visible but I also selected year 2019 in the table.

What is going wrong in my formula?

[V-lianl-msft]

Try modify the formula:

VAR MINYEAR = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table', 'Table'[Year], 'Table'[clientID]))

[Anonymous]

PS I am happy to make some progress, but somehow the row totals are still going wrong and year 2019 does not show up. What am I doing wrong with the previous mentioned measure?

[Ashish_Mathur]

Hi,

Paste the table of data in a format that can be taken to MS Excel.  Alternatively share the link from where i can download your MS Excel file.

[Anonymous]

Hi,

INPUT TABLE
clientID Date
1944 43120
5032 43120
5046 43363
8359 43124
8871 43420
17075 43420
1944 43147
1944 43485
5032 43485
5046 43728
8359 43489
8871 43785
17075 43785
1944 43512

PS got on keeping a invalid html was found in your message and had to paste the data from Excel to notepad to here, otherwise it will not work. Pasting the OUTPUT table did not work in this matter. The output table is simple, only the first calculated distinctcount per client per year should be visible (I will keep on trying to upload it, but I have to wait 1 hour after it converts the html otherwise I will get an overflooding messag of the forum 😞  )

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you very much for this clean solution!

For other users in the future: The formula that works (solution created by Ashish) is:
Measure =
VAR ROWCOUNT = COUNTROWS('DataTable')
VAR YTDMeasure = CALCULATE(ROWCOUNT,DATESYTD('Calendar'[Date],"31/12"))
RETURN
IF(YTDMeasure>1,BLANK(),ROWCOUNT)

[Ashish_Mathur]

You are welcome.

[Anonymous]

this reply

[amitchandak]

@Anonymous , That means you need YTD? Join date with date table and try

CALCULATE(DISTINCTCCOUNT(CLIENTID), datesytd(Date[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Unfortunately that does not work:

CALCULATE(DISTINCTCCOUNT(CLIENTID), datesytd(Date[Date]))  resulted in:

In February 2018 there now is a 1 in clientID 1944 but it should be 0