Question about Data Architecture within PBI! Please help!

[Anonymous]

Hello All, 

I do not have an exact use case, but a broader data architecture question. I am slowly learning PBI (coming from a database background) and am wondering how the best way to go about this would be.

Essentially, we receive reports every week with our companies current headcount. We want to be able to track changes across the files, i.e If i select Junes file, i want to see how that compares to May, or even the previous weeks file. 

The issue im coming across is dynamically comparing files (when i select junes file, know to look at mays file) and then determining if an employee has exited, been hired, or transferred across departments. In a normal scenario, one could simply join the two tables, look for changes and create a column to determine its "status" based ojn the join results. However, im not sure how to accomplish this dynamically in PBI

I can provide some dummy data if need be, but any help and guidance is appreciated! 

[Greg_Deckler]

@Anonymous Seems like you should be able to put all the files in a folder and use a Folder query with Combine binaries to append them all together into a single table. If you have a date should be relatively easy to compare between months. 

[Anonymous]

@Greg_Deckler Hi Greg, thats exactly what i had started with. I created a table of all the files, but the comparisons seem to be where im getting lost. How would the data model look for something like this? Is it as simple as joining the file's date to a calendar table, and using time intelligence to select the previous month, or week?

[Greg_Deckler]

@Anonymous Oh, well, I personally would not use TI functions as I hate them. But, in general, yes, that is the approach. Creating an incremental number for your year/month would definitely help. See my thoughts on TI functions here:

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Anonymous]

Hi @Greg_Deckler , Thank you for this, as i do find issues with time intelligence. I have attached a very simple set of sample data (across two files). Would you be able to show how you'd compare these files to see changes in dept, while also factoring in new hires and terminations? 

FIleDate	Associate ID	Full Name	Worker Type	Associate Type	Active Status	Hire Date	Termination Date	Business Title	Dept
6/28/2021	100054	Buscaglia, Richard A	Associate	Regular	Yes	6/26/2000		Client Relationship Specialist	Operations
6/28/2021	100075	Sage, Daniel P	Associate	Regular	Yes	6/6/2000		VP-Account Mgt.	Sales - Product & Solutions
6/28/2021	100101	Guarino, Frank	Associate	Regular	Yes	6/19/2000		VP, Data Analytics	Technology
7/28/2021	100054	Buscaglia, Richard A	Associate	Regular	Yes	6/26/2000		Client Relationship Specialist	Finance
7/28/2021	100075	Sage, Daniel P	Associate	Regular	Yes	6/6/2000	7/3/2021	VP-Account Mgt.	Sales - Product & Solutions
7/28/2021	100101	Guarino, Frank	Associate	Regular	Yes	6/19/2000		VP, Data Analytics	Technology
7/28/2021	100492	Castillo, Jose H	Associate	Regular	Yes	7/21/2021		Production Technician	Accounting

[Greg_Deckler]

@Anonymous First step, you can create a DAX calculated column like the following:

Index = RANKX('Table2',[FileDate],,DESC,Dense)

In this way, your most recent file will always be Index = 1 and the previous month will always be Index = 2. After that, the pattern is going to be similar to new customers, lost customers like here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

I can be more specific if you can tell/show me exactly what you are going for.

[Anonymous]

@Greg_Deckler  This is a great start, I appreciate it. Essentially, we have files of worker status reports that we get every week. I want to be able to select a given file date, and dynamically compare it to the previous file to see how many employees changed department, how many have left, and how many have been hired.

For transfers, were considering that to be a change in department. Exits we are considering an employee that was on the previous report, but not the current. New Hires would be the opposite, having not existed on the previous report, but on the current. 

Does this make sense? I can try creating a sample pbi, but the data is relatively sensitive. 

Appreciate all your help so far!

[Greg_Deckler]

@Anonymous OK, so if you have that column, you could do this for how many hired for example:

Hired Measure =
  VAR __Current = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Index]=1),"__ID",[Associate ID]))
  VAR __Previous = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Index]=2),"__ID",[Associate ID]))
RETURN
  COUNTROWS(EXCEPT(__Current,__Previous))

[Anonymous]

@Greg_Deckler This makes sense. I will give this a shot. Will this scale based on the month/date selected? I see the index is dynamically given to the max date, but if a user wanted to select "June", and see "May", would this hard coded index measure work?

[Greg_Deckler]

@Anonymous In that case, you would do this:

Hired Measure =
  VAR __MinIndex = MIN('Table2'[Index])
  VAR __Current = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Index]=__MinIndex),"__ID",[Associate ID]))
  VAR __Previous = DISTINCT(SELECTCOLUMNS(FILTER('Table2',[Index]=__MinIndex+1),"__ID",[Associate ID]))
RETURN
  COUNTROWS(EXCEPT(__Current,__Previous))

[Anonymous]

@Greg_Deckler , Thank you for all your help thus far. I feel as though im making progress, but am going in circles. To try and fully understand this, i created a sample PBI file with most of the data stripped out. The goal is to be able to take an associate ID (which would roll up to a business unit) and get the total headcount, transfers, exits and new hires by Business Unit. 

If you can provide any guidance in the attached PBI, it would be greatly appreciated.

Sample PBI 

[Greg_Deckler · Accepted solution]

@Anonymous Before we go too far down the rabbit hole with this, take a look at the attached PBIX and tell me if this is what you are going for. The following measure shows the people terminated in June even though those terminations show up in July file. Is that what you are going for or no?

Terminated = 
    VAR __Date = MAX('Sample'[Date])
    VAR __Rank = MAX('Sample'[Rank])
    VAR __CostCenter = MAX('Sample'[Cost Center - ID])
    VAR __EarliestDate = EOMONTH(__Date,-1)+1
    VAR __LatestDate = EOMONTH(__Date,0)
RETURN
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(ALL('Sample'),[Cost Center - ID] = __CostCenter && [Rank] = __Rank - 1 && [Termination Date]>=__EarliestDate && [Termination Date]<=__LatestDate),
                "__AssociateID",[Associate ID]
            )
        )
    )

PBIX is below sig

[Anonymous]

@Greg_Deckler This actually works perfectly in theory. However, we cannot rely on our termination dates, as employees could be rehired with the same ID, and the termination date stays due to poor maintenance at the source. 


What im up to at this point is trying to understand how to calculate the terms based on what exists in one file (previous month) and not the current. This has been one of my last hurdles. 

[v-shex-msft]

Hi @Anonymous,

Did Greg's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous Well, the standard way of comparing things to find differences is by using EXCEPT. I figured there was more to this which is why I didn't try to boil the ocean solving it.