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 am trying to build a table, which should show us the name of employees which were added or removed from the Previous month.
Below is an example
Assume this is the table I have. The current one is more than 1000 rows
Date | Username |
January | Sam |
January | Harvey |
January | Maria |
January | Dean |
January | Sameul |
January | Sam |
February | Harvey |
February | Sameul |
February | Jacob |
February | Sam |
March | Wills |
March | Joseph |
March | Maria |
March | Harvey |
In Power BI, I am adding a slicer called Month. So when I select Month from dropdown menu.
Month |
March |
I should get the following result
Changes from Previous Month | |
Name | Action |
Wills | Added |
Joseph | Added |
Sam | Removed |
Jacob | Removed |
Maria | Added |
What measure or DAX should I use?
Solved! Go to Solution.
Please see the file attached here as well
HI @ymirza
Try this solution
First add a Month Number Column in your Table so that we can identify previous month
Month Number = Switch(Table1[Date],"January",1,"February",2,"March",3)
Next Create a Supporting Calculated Table...From the Modelling Tab>>>New Table
SupportingTable = ALL(Table1[Username])
Now We can use use this MEASURE in your TABLE (say TABLE1)
Measure = VAR CurrentUsername = SELECTEDVALUE ( 'SupportingTable'[Username] ) VAR SelectedMonth = SELECTEDVALUE ( Table1[Month Number] ) VAR CurrentMonth = CALCULATE ( DISTINCTCOUNT ( Table1[Username] ), FILTER ( Table1, Table1[Username] = CurrentUsername ) ) VAR Previous_Month = CALCULATE ( DISTINCTCOUNT ( Table1[Username] ), FILTER ( ALL ( Table1 ), Table1[Username] = CurrentUsername && Table1[Month Number] = SelectedMonth - 1 ) ) VAR Result = IF ( OR ( Previous_Month > 0, CurrentMonth > 0 ), CurrentMonth - Previous_Month ) RETURN SWITCH ( Result, 1, "Added", -1, "Removed", 0, "No Change" )
@Zubair_Muhammad you my friend are a genius. I couldnt have thought about this solution myself. Thanks a million for detailed and clearly explained solution.
Please see the file attached here as well
Hi Zubair,
There is slight issue, when I am trying to add another filter "Country" and created a relationship with SupportingTable (UserName) with Table1 (userName) inorder to invoke country location, the REMOVED text under measure is disapperas while Added and 'No change' remains. I tried everything but it it doesnt work. Can you help?
Hi @ymirza
Could you share your file?
You can upload to onedrive/ googledrive etc and share a link here
Please check this link. I have uploaded the file here.
https://drive.google.com/file/d/1JQYJ8PL-g-hqMQ0srB9drrtuZrtFkeeI/view?usp=sharing
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |