How to obtain the difference with previous data according to type and dates

[norambuena]

Hello .
sorry but i'm new to this
I have the following problem...
I need to obtain the difference between the last value entered by Type and its previous value (of course, the date is not always the day before because there are weekends and holidays that are not entered)
Ex: 02-03-2022 Type A (6x6) -24 I have to subtract 02.02.2023 TYPE A (6X6) 185 , the same to TIPO B, TIPO C , etc....
And so for each tiop the same subtract the previous day and what to do when there are holidays without data of being Saturday or Sunday should take the value of Friday, in this case there would be a difference of 2 days with the previous data

Calculate difference between current and previous row with a condition 

[v-stephen-msft · Accepted solution]

Hi @norambuena ,

Based on your sample data, I created the following table. To simulate no input on Saturday and Sunday, I also added February 6th.

Below is the solution.

1.Group by.

2.Add a custom to return the index.

3.Dupliacte it to get Table 2. In Table 2, go to step of "Added Custom", replace the "0" with "1" as follows.

4.Expand them and get two following tables.

5.Merge them, set up as follows. Matching columns are Type and Index and note the numerical order of the them.

6.Expand the column after merged to get the previous value.

7.You can add a custom column to get the differences.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[alannavarro]

Hello, hope it works.

let
//Source Data. I used as column names ID, Date, Material and Value.
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// I have dates in english, is just a changed type to D/M/Y instead of M/D/Y.
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "es-MX"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Material"}, {{"Count", each _, type table [ID=number, Date=nullable date, Material=text, Value=number]}}),
//Custom function to group all materials, sort them and then merge them with an index.
Custom =
(compare)=>
let
#"Sorted Rows" = Table.Sort( compare ,{{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
Custom1 = Table.NestedJoin(#"Added Index", "Index", #"Added Index1","Index.1", "Custom"),
#"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Value"}, {"Value.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",null,0,Replacer.ReplaceValue,{"Value.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index"})
in
#"Removed Columns",
Add_custom_column = Table.AddColumn(#"Grouped Rows", "Custom", each Custom([Count])),
#"Removed Other Columns" = Table.SelectColumns(Add_custom_column,{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Date", "Material", "Value", "Value.1"}, {"ID", "Date", "Material", "Value", "Value.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"ID", Order.Ascending}})
in
#"Sorted Rows"