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.
Hi,
I have a table with some information, based on Months. Now I need a way to find out what is different from each month (which information was added and which was removed).
Eg: My table consists of details of employees with the month. Now i have total of 60 records for June, and for July i will have 63. I want to find out what was that additional 3. It can be a case of 2 employess moving out and 5 getting added or 3 new records getting added with respect to the previous month
Basically, a vlookup kind of a thing to give the details rather than only numbers.
I hope I am clear.
Thanks !!
I have a table with some information, based on Months. Now I need a way to find out what is different from each month (which information was added and which was removed).
According to your description, you can create a measure to get the new added or moved out employees for each month.
Assume you have a table called EmployeeDetails like below:
You can use the formula below to get new added Employees for each month:
New Added Employee = IF ( HASONEVALUE ( EmployeeDetails[Month] ), IF ( DATEADD ( VALUES ( EmployeeDetails[Date] ), -1, MONTH ) <> BLANK (), IF ( CALCULATE ( COUNTROWS ( VALUES ( EmployeeDetails[EmployeeName] ) ), FILTER ( VALUES ( EmployeeDetails[EmployeeName] ), EmployeeDetails[EmployeeName] <> CALCULATETABLE ( VALUES ( EmployeeDetails[EmployeeName] ), FILTER ( ALL ( EmployeeDetails[date] ), EmployeeDetails[Date] = DATEADD ( VALUES ( EmployeeDetails[Date] ), -1, MONTH ) ), ALL ( EmployeeDetails[Month] ) ) ) ) > 0, "Added" ) ) )
And the moved out employees:
Moved Out Employee = IF ( HASONEVALUE ( EmployeeDetails[Month] ), IF ( DATEADD ( VALUES ( EmployeeDetails[Date] ), 1, MONTH ) <> BLANK (), IF ( CALCULATE ( COUNTROWS ( VALUES ( EmployeeDetails[EmployeeName] ) ), FILTER ( VALUES ( EmployeeDetails[EmployeeName] ), EmployeeDetails[EmployeeName] <> CALCULATETABLE ( VALUES ( EmployeeDetails[EmployeeName] ), FILTER ( ALL ( EmployeeDetails[date] ), EmployeeDetails[Date] = DATEADD ( VALUES ( EmployeeDetails[Date] ), 1, MONTH ) ), ALL ( EmployeeDetails[Month] ) ) ) ) > 0, "Moved Out" ) ) )
Then you can use these measures to show the result you want in the report:
Regards
Hi Jerry, Thanks for the piece of code. I tried to use it, but numbers was not as I expected. I guess i have missed something.
I will re-check on the same again.
Thanks !!
This is something similar to finding the number of new customers and lost customers between current month and previous month.
The link
https://javierguillen.wordpress.com/2012/08/24/determining-customer-retention-in-dax/
Check it out and you can get your answer.
If this solves your issue please accept this as a solution and also give Kudos.
Cheers
CheenuSing
Hi,
The link was helpful, I was able to get the numbers. But only thing which I could not do was to extract the data behind that number.
I need something dynamic, like when i select a particular month i should get the added and moved out data.
But, really thanks for your reply..It helped me very much.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |