Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi DAX Champions!
I have a problem that has been puzzling me for almost half a day now.
I have essentially what is an inventory table (but an inventory of people), with rows that record their salary (or rather change in salary) and particular given dates. This is akin to an inventory table, where the quantity of each product might change in a particular date.
Now, I know there are methods on SQLBI that show you how to calculate total quantity at particular snapshots, using an inventory table, and movement table. I've tried the same, and it works. It's just that I need to perform other calculations like (number of men, number of women, at different snapshots, or number of people belonging to a particular department), and this is SO, so much easy to do when you have a SNAPSHOT table, rather than movement table.
Any ideas therefore on how to do the conversion, either using DAX or M, or even something else.
Attached is a picture of my movement table.
Would really appreciate any help!
Solved! Go to Solution.
I think I solved it.
Had to Start with a unique list of Employee IDs in a fresh query
Then use the Add Column function to add dates from the Calendar Table (preloaded into a Query as a connection),
So, now I have a table with a Cartesian Product of Employee IDs, and all Dates in the Calendar Table!
Employee All Dates in Calendar Table
1 3500 rows
2 3500 rows
3 3500 rows
Then
Use Merge Queries (not add Column) on the above two data fields, with the Movement Table (see link below), with the common fields (Employee ID, and Date Effective field)
The I used fill down operation to fill the data inbetween any gaps between dates.
Here is my origional movement table
https://www.dropbox.com/preview/Public/Picture1.png?role=personal
Seems to work!
Only have to make sure that the Movement table doesn't contain any nulls, but rather '0's, otherwise the fill down operation won't work correctly across employees.
Actually, just checking, the filldown operation doesn't quite work, at the boundaries between employees, so have to figure how to get the fill down operation to only fill down upto the last row of a particular employee! Then it should work!
Pic link didn't work for me, can you just show in text or direct picture insert what your movement table looks like as well as what you want your snapshot table to look like? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I think I solved it.
Had to Start with a unique list of Employee IDs in a fresh query
Then use the Add Column function to add dates from the Calendar Table (preloaded into a Query as a connection),
So, now I have a table with a Cartesian Product of Employee IDs, and all Dates in the Calendar Table!
Employee All Dates in Calendar Table
1 3500 rows
2 3500 rows
3 3500 rows
Then
Use Merge Queries (not add Column) on the above two data fields, with the Movement Table (see link below), with the common fields (Employee ID, and Date Effective field)
The I used fill down operation to fill the data inbetween any gaps between dates.
Here is my origional movement table
https://www.dropbox.com/preview/Public/Picture1.png?role=personal
Seems to work!
Only have to make sure that the Movement table doesn't contain any nulls, but rather '0's, otherwise the fill down operation won't work correctly across employees.
Actually, just checking, the filldown operation doesn't quite work, at the boundaries between employees, so have to figure how to get the fill down operation to only fill down upto the last row of a particular employee! Then it should work!
Final solution
Which also ensures fill down operation stops at the boundaries between different employees can be found here
Now closed!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |