Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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.
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.
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
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.
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.
Hi,
Share data in a format that can be pasted in an MS Excel file.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |