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 All,
I'm looking to take monthly files of employees, and track changes in departments to derive transfers. I understand how i can easily do this with two files by joining them together, and creating a calculated column to track if the dept changed. However, i need this to scale.
Currently, I have a folder of files for every month, and assign each record a file date of the source. How would i dynamically be able to, for example, select the month of May, and compare that to my records with a file date of April.
Example Dataset
File Date | Associate | Dept |
1/1/2021 | 1 | 2 |
1/1/2021 | 2 | 1 |
2/1/2021 | 1 | 2 |
2/1/2021 | 2 | 2 |
3/1/2021 | 1 | 2 |
3/1/2021 | 2 | 2 |
In this example, id want to be able to select "February" and see that In February (Compared to January), I had One transfert (Dept Changed for Employee 2). When I select March, Id like to see No transfers (Compared to February).
Any guidance or advice is greatly appreciated!
@Anonymous,
This solution requires a date table with a relationship to the fact table.
Data model:
Measure:
Transfer Count =
VAR vTableCurrentMonth =
SUMMARIZE ( FactTable, FactTable[Associate], FactTable[Dept] )
VAR vTablePrevMonth =
CALCULATETABLE (
SUMMARIZE ( FactTable, FactTable[Associate], FactTable[Dept] ),
PREVIOUSMONTH ( DimDate[Date] )
)
VAR vDelta =
EXCEPT ( vTableCurrentMonth, vTablePrevMonth )
VAR vRowCount =
COUNTROWS ( vDelta )
VAR vResult =
IF ( ISBLANK ( vRowCount ), 0, vRowCount )
RETURN
vResult
Create visuals. The date slicer should use the date table.
--------------------------------------
Proud to be a Super User!
@DataInsights , Hi I tried to implement this but it isnt working as intended. Its not accurately tracking transfers as i went back and checked the data. its tracking associate ids that have NOT changed as a transfer. Any idea why this would be happening? Appreciate the help!
@Anonymous,
Would you be able to share a link to your pbix with sample data? If not, I'll need sample data I can paste into Power BI and a screenshot of your data model. Also include the expected result.
Proud to be a Super User!
Here is my data model. Very simple at the moment. Just one table with every month's file (separated by file name), and a calendar table to join to it
Im looking to be able to take each business unit and show the amount of transfers (business unit changed), new hires (hire date Month/Year is in selected Month/Year) and exits (termination date is in selected Month/Year). Lastly, id like to show the total headcount (non exits, transfers, and new hires)
Ex:
Business | Sub BU | Dept | Transfer | New Hire | Exits | Total Headcount |
Finance | Consolidation | 3 | 2 | 4 | 2 | 100 |
I feel as though this should be relatively straight forward, but every avenue i go down doesnt seem to fit exactly. Any help or guidance would be huge. Appreciate it!
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |