Forum Discussion
Comparing Files for Changes Month over Month
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.
--------------------------------------
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.
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!