Forum Discussion
Anonymous
7 years agoNot applicable
days between Task and total
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 | |||
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
4 Replies
- AnonymousNot applicable
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:
- AnonymousNot applicable
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.- v-frfei-msftCommunity Support
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