Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |