Forum Discussion
Question about Data Architecture within PBI! Please help!
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!
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
14 Replies
- Greg_Deckler
Community Champion
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.
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