cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sachintandon84 Frequent Visitor
Frequent Visitor

How to convert a movement table to a snapshot table?

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
sachintandon84 Frequent Visitor
Frequent Visitor

Re: How to convert a movement table to a snapshot table?

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!

 

 

View solution in original post

3 REPLIES 3
Super User
Super User

Re: How to convert a movement table to a snapshot table?

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 have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
sachintandon84 Frequent Visitor
Frequent Visitor

Re: How to convert a movement table to a snapshot table?

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!

 

 

View solution in original post

sachintandon Frequent Visitor
Frequent Visitor

Re: How to convert a movement table to a snapshot table?

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 201 members 2,214 guests
Please welcome our newest community members: