Reply
Frequent Visitor
Posts: 5
Registered: Wednesday

How to convert a movement inventory table to a snapshot inventory 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!

 

 

 

Community Support Team
Posts: 1,485
Registered: ‎07-10-2018

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

Hi @sachintandon84,

 

I cannot get the pic as you shared in SP. Could you please share your sample data to me by one dirve or something else so that I can check your question again.

 

Regards,

Frank

Frequent Visitor
Posts: 5
Registered: Wednesday

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

Highlighted
Community Support Team
Posts: 1,485
Registered: ‎07-10-2018

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

Hi @sachintandon84,

 

I made one sample for your reference.

 

To create measure as below and make the table visual filtered by the measure.

 

Measure = var maxdate =CALCULATE(MAX(new[Date Effective]),ALLEXCEPT(new,new[employee]))
return
IF(MAX(new[Date Effective])=maxdate,1,0)

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Attachment
Frequent Visitor
Posts: 5
Registered: Wednesday

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

Hi there, 

 

I used Power Query instead.

 

The solution is more powerful, as the conversion is done upstream, before importing into the data model. 

It also simplifies the creation of any DAX measures, immensely - I CANNOT OVERSTATE this bit. 

 

I have an Employee Table (as a connection query), a Calendar Table (as a connection query), and then the Movement Table (as a connection query).....

 

There's a big filter line, as I wanted a snapshot at the beginning of each month (except for april where I wanted it on the 5th). 

I didn't want a snapshot for every day, otherwise the inventory table gets too big!

 

KR, 

 

-ST

 

Here is the M-Code:

 

let
Source = Employee_Table,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Employee Key"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Calendar_Table),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Calendar Dates"}, {"Calendar Dates"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Calendar Dates", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Employee Key", "Calendar Dates"},Salary_Table,{"Employee Key", "Date Effective"},"Salary_Table",JoinKind.FullOuter),
#"Expanded Salary_Table" = Table.ExpandTableColumn(#"Merged Queries", "Salary_Table", {"Record No", "Office", "Employee Key", "Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Salary Increase", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}, {"Record No", "Office", "Employee Key.1", "Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Salary Increase", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}),

#"Grouped Rows" = Table.Group(#"Expanded Salary_Table", {"Employee Key"}, {{"Rows", each Table.FillDown(_, {"Office", "Record No", "Office","Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}), type table}}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Calendar Dates", "Record No", "Office","Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Salary Increase", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}, {"Calendar Dates","Record No", "Office", "Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Salary Increase", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Rows",{"Actual Base Salary (before increases)"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Salary Increase"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Actual Base Salary (before increases)", each [#"Actual Base Salary (after increases)"] - [Salary Increase]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Employee Key", "Calendar Dates", "Record No", "Office", "Title", "FTE %", "Currency", "Actual Base Salary (before increases)", "Salary Increase", "Actual Base Salary (after increases)", "Date Effective", "Salary is Confirmed or Tentative?", "Last Annual STIP Bonus", "Other Annual Comp", "Other Comp Desc."}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Employee Key", Int64.Type}, {"Calendar Dates", type date}, {"Record No", Int64.Type}, {"Office", type text}, {"Title", type text}, {"FTE %", Percentage.Type}, {"Currency", type text},{"Actual Base Salary (before increases)", type number}, {"Salary Increase", type number}, {"Actual Base Salary (after increases)", type number}, {"Date Effective", type date}, {"Salary is Confirmed or Tentative?", type text}, {"Last Annual STIP Bonus", type number}, {"Other Annual Comp", type number}, {"Other Comp Desc.", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Record No] <> null) and ([Calendar Dates] = #date(2016, 2, 1) or [Calendar Dates] = #date(2016, 3, 1) or [Calendar Dates] = #date(2016, 4, 5) or [Calendar Dates] = #date(2016, 5, 1) or [Calendar Dates] = #date(2016, 6, 1) or [Calendar Dates] = #date(2016, 7, 1) or [Calendar Dates] = #date(2016, 8, 1) or [Calendar Dates] = #date(2016, 9, 1) or [Calendar Dates] = #date(2016, 10, 1) or [Calendar Dates] = #date(2016, 11, 1) or [Calendar Dates] = #date(2016, 12, 1) or [Calendar Dates] = #date(2017, 1, 1) or [Calendar Dates] = #date(2017, 2, 1) or [Calendar Dates] = #date(2017, 3, 1) or [Calendar Dates] = #date(2017, 4, 5) or [Calendar Dates] = #date(2017, 5, 1) or [Calendar Dates] = #date(2017, 6, 1) or [Calendar Dates] = #date(2017, 7, 1) or [Calendar Dates] = #date(2017, 8, 1) or [Calendar Dates] = #date(2017, 9, 1) or [Calendar Dates] = #date(2017, 10, 1) or [Calendar Dates] = #date(2017, 11, 1) or [Calendar Dates] = #date(2017, 12, 1) or [Calendar Dates] = #date(2018, 1, 1) or [Calendar Dates] = #date(2018, 2, 1) or [Calendar Dates] = #date(2018, 3, 1) or [Calendar Dates] = #date(2018, 4, 5) or [Calendar Dates] = #date(2018, 5, 1) or [Calendar Dates] = #date(2018, 6, 1) or [Calendar Dates] = #date(2018, 7, 1) or [Calendar Dates] = #date(2018, 8, 1) or [Calendar Dates] = #date(2018, 9, 1) or [Calendar Dates] = #date(2018, 10, 1) or [Calendar Dates] = #date(2018, 11, 1) or [Calendar Dates] = #date(2018, 12, 1) or [Calendar Dates] = #date(2019, 1, 1) or [Calendar Dates] = #date(2019, 2, 1) or [Calendar Dates] = #date(2019, 3, 1) or [Calendar Dates] = #date(2019, 4, 5) or [Calendar Dates] = #date(2019, 5, 1) or [Calendar Dates] = #date(2019, 6, 1) or [Calendar Dates] = #date(2019, 7, 1) or [Calendar Dates] = #date(2019, 8, 1) or [Calendar Dates] = #date(2019, 9, 1) or [Calendar Dates] = #date(2019, 10, 1) or [Calendar Dates] = #date(2019, 11, 1) or [Calendar Dates] = #date(2019, 12, 1) or [Calendar Dates] = #date(2020, 1, 1) or [Calendar Dates] = #date(2020, 2, 1) or [Calendar Dates] = #date(2020, 3, 1) or [Calendar Dates] = #date(2020, 4, 5) or [Calendar Dates] = #date(2020, 5, 1) or [Calendar Dates] = #date(2020, 6, 1) or [Calendar Dates] = #date(2020, 7, 1) or [Calendar Dates] = #date(2020, 8, 1) or [Calendar Dates] = #date(2020, 9, 1) or [Calendar Dates] = #date(2020, 10, 1) or [Calendar Dates] = #date(2020, 11, 1) or [Calendar Dates] = #date(2020, 12, 1) or [Calendar Dates] = #date(2021, 1, 1) or [Calendar Dates] = #date(2021, 2, 1) or [Calendar Dates] = #date(2021, 3, 1) or [Calendar Dates] = #date(2021, 4, 5) or [Calendar Dates] = #date(2021, 5, 1) or [Calendar Dates] = #date(2021, 6, 1) or [Calendar Dates] = #date(2021, 7, 1) or [Calendar Dates] = #date(2021, 8, 1) or [Calendar Dates] = #date(2021, 9, 1) or [Calendar Dates] = #date(2021, 10, 1) or [Calendar Dates] = #date(2021, 11, 1) or [Calendar Dates] = #date(2021, 12, 1) or [Calendar Dates] = #date(2022, 1, 1) or [Calendar Dates] = #date(2022, 2, 1) or [Calendar Dates] = #date(2022, 3, 1) or [Calendar Dates] = #date(2022, 4, 5) or [Calendar Dates] = #date(2022, 5, 1) or [Calendar Dates] = #date(2022, 6, 1) or [Calendar Dates] = #date(2022, 7, 1) or [Calendar Dates] = #date(2022, 8, 1) or [Calendar Dates] = #date(2022, 9, 1) or [Calendar Dates] = #date(2022, 10, 1) or [Calendar Dates] = #date(2022, 11, 1) or [Calendar Dates] = #date(2022, 12, 1) or [Calendar Dates] = #date(2023, 1, 1) or [Calendar Dates] = #date(2023, 2, 1) or [Calendar Dates] = #date(2023, 3, 1) or [Calendar Dates] = #date(2023, 4, 5) or [Calendar Dates] = #date(2023, 5, 1) or [Calendar Dates] = #date(2023, 6, 1) or [Calendar Dates] = #date(2023, 7, 1) or [Calendar Dates] = #date(2023, 8, 1) or [Calendar Dates] = #date(2023, 9, 1) or [Calendar Dates] = #date(2023, 10, 1) or [Calendar Dates] = #date(2023, 11, 1) or [Calendar Dates] = #date(2023, 12, 1) or [Calendar Dates] = #date(2024, 1, 1) or [Calendar Dates] = #date(2024, 2, 1) or [Calendar Dates] = #date(2024, 3, 1) or [Calendar Dates] = #date(2024, 4, 5) or [Calendar Dates] = #date(2024, 5, 1) or [Calendar Dates] = #date(2024, 6, 1) or [Calendar Dates] = #date(2024, 7, 1) or [Calendar Dates] = #date(2024, 8, 1) or [Calendar Dates] = #date(2024, 9, 1) or [Calendar Dates] = #date(2024, 10, 1) or [Calendar Dates] = #date(2024, 11, 1) or [Calendar Dates] = #date(2024, 12, 1) or [Calendar Dates] = #date(2025, 1, 1) or [Calendar Dates] = #date(2025, 2, 1) or [Calendar Dates] = #date(2025, 3, 1) or [Calendar Dates] = #date(2025, 4, 5) or [Calendar Dates] = #date(2025, 5, 1) or [Calendar Dates] = #date(2025, 6, 1) or [Calendar Dates] = #date(2025, 7, 1) or [Calendar Dates] = #date(2025, 8, 1) or [Calendar Dates] = #date(2025, 9, 1) or [Calendar Dates] = #date(2025, 10, 1) or [Calendar Dates] = #date(2025, 11, 1) or [Calendar Dates] = #date(2025, 12, 1) or [Calendar Dates] = #date(2026, 1, 1) or [Calendar Dates] = #date(2026, 2, 1) or [Calendar Dates] = #date(2026, 3, 1) or [Calendar Dates] = #date(2026, 4, 5) or [Calendar Dates] = #date(2026, 5, 1) or [Calendar Dates] = #date(2026, 6, 1) or [Calendar Dates] = #date(2026, 7, 1) or [Calendar Dates] = #date(2026, 8, 1) or [Calendar Dates] = #date(2026, 9, 1) or [Calendar Dates] = #date(2026, 10, 1) or [Calendar Dates] = #date(2026, 11, 1) or [Calendar Dates] = #date(2026, 12, 1)))
in
#"Filtered Rows"