How to convert a movement table to a snapshot table?

[sachintandon84]

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. 

 Pic

Would really appreciate any help!

[Greg_Deckler]

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

[sachintandon84 · Accepted 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!

[sachintandon]

Final solution

Which also ensures fill down operation stops at the boundaries between different employees can be found here

https://community.powerbi.com/t5/Desktop/How-to-convert-a-movement-inventory-table-to-a-snapshot/m-p...

Now closed!