Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Comparing Files for Changes Month over Month

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 DateAssociate

Dept

1/1/20211

2

1/1/20212

1

2/1/20211

2

2/1/20212

2

3/1/20211

2

3/1/20212

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!

 

4 REPLIES 4
DataInsights
Super User
Super User

@Anonymous,

 

This solution requires a date table with a relationship to the fact table.

 

Data model:

 

DataInsights_0-1626280460278.png

 

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_1-1626280499890.png

--------------------------------------

 

DataInsights_2-1626280518260.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@DataInsights , 

 

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

Slevin37_0-1627474950128.png

 

 

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:

 

BusinessSub BUDeptTransferNew HireExitsTotal Headcount
FinanceConsolidation3242100



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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.