How to substract row by row based on task and group by Stores

[Manjarigoyal1]

Hi,

I would like the solution in MQuery. I have found multiple solutions where you can index and groupby but it only shows from first row to next row not the skip level.

I want to calculate the SLA for each Store based on TaskNo. I need to know for each store from task 1 to task 3 how much time did it take to close. for example Store no 3725033 task 1 is closed on 12/15/2020 and task 3 is closed on 1/11/2023 so I want to calculate the column "Sla from taskNo 1 to TaskNo 3" by task3 closed date - task1 closed date. then I want to calculate "Sla from taskNo 4 to TaskNo 6" by task6 closed date - task4 closed date and so on shown the red columns in the table. 

Store no	TaskNo	ClosedDate	Sla from taskNo 1 to TaskNo 3	Sla from taskNo 4 to TaskNo 6	Sla from taskNo 7 to TaskNo 9	Total SLA from task 1 to task 9
3725033	1	12/15/2020
3725033	2	1/11/2023
3725033	3	1/11/2023
3725033	4	1/26/2023
3725033	5	4/25/2023
3725033	6	5/30/2023
3725033	7	6/2/2023
3725033	8	6/5/2023
3725033	9	null
3725034	1	1/11/2023
3725034	2	1/26/2023
3725034	3	5/30/2023
3725034	4	6/2/2023
3725034	5	null
3725034	6	null
3725034	7	null
3725034	8	null
3725034	9	null
3725035	1	5/30/2023
3725035	2	5/30/2023
3725035	3	6/2/2023
3725035	4	7/2/2023
3725035	5	7/6/2023
3725035	6	7/16/2023
3725035	7	7/26/2023
3725035	8	7/30/2023
3725035	9	8/15/2023

[WanderingBI · Accepted solution]

To create the first SLA (Task 1 to 3):

You can create a new column which holds a filtered list of the original table:

• First you can filter the list by the store of the current row and task 1 and 3. This gives you a table like this for the current row in the column with only two dates.
• You can then transform the column [ClosedDate] to a list and get the maximum and minimum dates.
• Then you can calculate the distance between the two dates.

I have created an example here where I put the calculation for the dates in a seperate function for clarity.

Note that you can also create a function to dynamically create the other SLA columns so your code does not get so long. 

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "fdI7DoMwEEXRvbhGmp/HNmtB7ACly/6DSOIMD5OCZo4E70osS7KqzmZpSrI/ysokSuJpnSLqG41EiAXQ/mH+IgtpAfSOmRS/WTo6GQPWjoVYAVtEfO28P4/ntoVz/uUfO0UA9S4ix3zcmU/5sDN/8gdTyvhcx+c2Pg8y/ZQJY/2UOUC7K/FTZr2iRyyAJaAg1oCK2AJe1s4d2/E3ry8=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Store = _t, Task = _t, ClosedDate = _t]
    ),
    importClean = Table.TransformColumnTypes(
        Source, {{"Store", Int64.Type}, {"Task", Int64.Type}, {"ClosedDate", type date}}
    ),
    #"Filtered rows" = Table.SelectRows(importClean, each ([Store] <> 0)),
    importCleanFiltered = #"Filtered rows",
    
    calculateDates = (inputTable) =>
        let
            getDates = Table.Column(inputTable, "ClosedDate"),
            //calculationDebug = [max= List.Max(getDates), min=List.Min(getDates)],
            calculation = Duration.Days(List.Max(getDates) - List.Min(getDates))
        in
            calculation,

    SLA_1_to_3 = Table.AddColumn(
        importCleanFiltered,
        "SLA_1_to_3",
        (outerrow) =>
            if outerrow[Task] = 1 then
                calculateDates(
                    Table.SelectRows(
                        importCleanFiltered, (row) => row[Store] = outerrow[Store] and (row[Task] = 1 or row[Task] = 3)
                    )
                )
            else
                null
    )
in
    SLA_1_to_3

Let me know if you get stuck at any step of the code.

[amitchandak]

@Manjarigoyal1 ,

First create a new column

Number.IntegerDivide(TaskNo+2, 3)

then follow the group by method and have rest of the columns as a table.

Not inside that table use List.Average

Power BI and Power Query- Sub Category Rank and Index: https://youtu.be/tMBviW4-s4A