days between Task and total

[Anonymous]

Hi Team,

Need help in calcuating the days between the task . if end date is missing then put as blank.

Incident Number	Task	Start Date	End Date
1000	1	1/1/2019	1/5/2019
1000	2	1/6/2019	1/10/2019
1000	3	1/15/2019	1/20/2019
2000	1	1/25/2019
2000	2	1/30/2019	2/1/2019
2000	3	2/3/2019	2/5/2019
output
Incident Number	Task	Start Date	End Date	Daysbetween
1000	1	1/1/2019	1/5/2019	4.00
1000	2	1/6/2019	1/10/2019	5
1000	3	1/15/2019	1/20/2019	10
Total				19
2000	1	1/25/2019
2000	2	1/30/2019	2/1/2019
2000	3	2/3/2019	2/5/2019	4
Total				4

[Anonymous]

Please see the attached file and the applied steps in power query, but here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLzkxJzStR8CvNTUotUtJRCkkszgZSwSWJRSUKLoklqUCOa14KjKmgFKsTrWRoYGAA5BiCsL6hvpGBoSWYaQpjoigzAsuZIZQZGmBVZwyRNEUoNEJTqABmIWOQqBGya4yQ3ICmAOIOY7iZRgino6gzBssZI5QheysWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Incident Number", Int64.Type}, {"Task", Int64.Type}, {"Start Date", type date}, {"End Date", type date}, {" ", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{" "}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Incident Number] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Incident Number", Order.Ascending}, {"Task", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Incident Number"}, {{"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(
[All Rows],
"Index",
1, 1 )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn(
[Custom],
"Index2",
0, 1 )),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.NestedJoin(
[Custom.1], {"Index2"}, 
[Custom.1], {"Index"},
"Merged",
JoinKind.LeftOuter)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.ExpandTableColumn([Custom.2], "Merged", {"End Date"}, {"Prev End Date"})),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Table.Sort([Custom.3],{{"Task", Order.Ascending}})),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.5", each Table.AddColumn(
[Custom.4],
"End Date Use",
each
if [End Date] = null  then null else if [Prev End Date] = null then [End Date] else [Prev End Date] )),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Custom.6", each Table.AddColumn(
[Custom.5], 
"DaysBetween",
each if [Prev End Date] = null and [Task] = 1 then [End Date] - [Start Date] 
else
[End Date] - [Prev End Date]
)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom6",{"Custom.6"}),
    #"Expanded Custom.6" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.6", {"Incident Number", "Task", "Start Date", "End Date", "DaysBetween"}, {"Incident Number", "Task", "Start Date", "End Date", "DaysBetween"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.6",{{"Incident Number", Int64.Type}, {"Task", Int64.Type}, {"DaysBetween", Int64.Type}, {"End Date", type date}, {"Start Date", type date}})
in
    #"Changed Type2"

You really dont want to have totals/subtotals built into the tables ( there are specific situations where you would though) so just better to let the visuals take care of the totals

Here's the file:

https://1drv.ms/u/s!Amqd8ArUSwDS0AvvfmqTtcjGCqmj

[Anonymous]

Thanks Nick,

Can  we also extract the date based on the task and show in the same row. Like in below

Incident Number		Start Date1	End Date1	EndDate2	Enddate3
1000		1/1/2019	1/5/2019	1/10/2019	1/20/2019
2000		1/25/2019		2/1/2019	2/5/2019

Thanks,
Rajveer.

[Anonymous]

@Anonymous ,

1. I was able to do it, but it is not the most robust thing, as it will only work for three dates but should at least be starting point. PBIX fille is below, but the general idea:

   1. Reference the table we created yesterday
   2. Group that table by Incident number, and we want to take the Min of the Start Date column and then we also want all the rows for a sub-table
   3. Then add a few custom columns if task 2 and 3, accounting for if there are nulls or not. 
   4. Expand each of those, but only want the End Date we calculated

File is easier to step throught, but the final output:

File:

https://1drv.ms/u/s!Amqd8ArUSwDS0BA8cqSNTV80tXQ_

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated table as below.

Table = 
SUMMARIZE (
    Table1,
    Table1[Incident Number],
    "startdate1", CALCULATE (
        MAX ( 'Table1'[Start Date] ),
        FILTER ( 'Table1', 'Table1'[Task] = 1 )
    ),
    "end date1", CALCULATE ( MAX ( 'Table1'[End Date] ), FILTER ( Table1, Table1[Task] = 1 ) ),
    "enddate2", CALCULATE ( MAX ( 'Table1'[End Date] ), FILTER ( Table1, Table1[Task] = 2 ) ),
    "enddate3", CALCULATE ( MAX ( 'Table1'[End Date] ), FILTER ( Table1, Table1[Task] = 3 ) )
)

Regards,

Frank