Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!

 

General Comment
Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    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.

     

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        5 years ago

        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.