Historical table and new arrivals and new leavers

[psorel]

I need for an indicator, to calculate and display the number of newcomers and new leavers starting from a table
similar to the attached one. I would like to be able to display this data month by month and since the current year.
I still have trouble understanding the different context provided by DAX functions and can't get the expected result.

Cordially,

[v-jianboli-msft · Accepted solution]

Hi @psorel ,

Please try:

Nb newcomers = 
var _a = MAXX(FILTER(ALL('Table'[Date]),[Date]<SELECTEDVALUE('Table'[Date])),[Date])
var _b = EXCEPT(SELECTCOLUMNS('Table',"NDC",[NDC]),SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_a),"NDC",[NDC]))
return IF(ISBLANK(_a),0,COUNTROWS(_b))

Nb leavers = 
var _a = MAXX(FILTER(ALL('Table'[Date]),[Date]<SELECTEDVALUE('Table'[Date])),[Date])
var _b = EXCEPT(SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_a),"NDC",[NDC]),SELECTCOLUMNS('Table',"NDC",[NDC]))
return IF(ISBLANK(_a),0,COUNTROWS(_b))

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @psorel ,

Please try:

Nb newcomers = 
var _a = MAXX(FILTER(ALL('Table'[Date]),[Date]<SELECTEDVALUE('Table'[Date])),[Date])
var _b = EXCEPT(SELECTCOLUMNS('Table',"NDC",[NDC]),SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_a),"NDC",[NDC]))
return IF(ISBLANK(_a),0,COUNTROWS(_b))

Nb leavers = 
var _a = MAXX(FILTER(ALL('Table'[Date]),[Date]<SELECTEDVALUE('Table'[Date])),[Date])
var _b = EXCEPT(SELECTCOLUMNS(FILTER(ALL('Table'),[Date]=_a),"NDC",[NDC]),SELECTCOLUMNS('Table',"NDC",[NDC]))
return IF(ISBLANK(_a),0,COUNTROWS(_b))

Final output:

Best Regards,

Jianbo Li

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

[psorel]

Hi @v-jianboli-msft 
Thank you it works , 

is it possible to have an explanation? I'm still struggling to understand the notion of filter/row context introduced by some DAX functions

Best regards,

PSOREL

[v-jianboli-msft · Accepted solution]

Hi @psorel ,

The var _a is used to get the max date before current date.

Then var _b use the EXCEPT function to compare the before and current NDC

Then Countrows will give you the number of the difference.

Since the earliest date is not preceded by a day, use if statement to change the blank value to 0.

Best Regards,

Jianbo Li

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

[psorel]

Hello, 

I would like to obtain a table visual in pbi like this : 

At the 31/01/2023 -> there is 4 newcomers compared to 31/12/2023 ( E,F,G,H) and 4 leavers ( I, J,K,L )

Same thing by comparing the data of february and january.

Sorry but pbix file is not supported here. 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[v-jianboli-msft]

Hi @psorel ,

Could you please explain in detail how to determine new arrivals and new leavers? I am not sure how to determine them just by your previous description.

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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