Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 NumberTaskStart DateEnd Date 
100011/1/20191/5/2019 
100021/6/20191/10/2019 
100031/15/20191/20/2019 
     
200011/25/2019  
200021/30/20192/1/2019 
200032/3/20192/5/2019 
     
     
output    
     
Incident NumberTaskStart DateEnd DateDaysbetween
100011/1/20191/5/20194.00
100021/6/20191/10/20195
100031/15/20191/20/201910
Total   19
200011/25/2019  
200021/30/20192/1/2019 
200032/3/20192/5/20194
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

  • Anonymous's avatar
    Anonymous
    Not 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:

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

    • Anonymous's avatar
      Anonymous
      Not 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 Date1End Date1EndDate2Enddate3
      1000 1/1/20191/5/20191/10/20191/20/2019
      2000 1/25/2019 2/1/20192/5/2019
            

       

      Thanks,
      Rajveer.

      • v-frfei-msft's avatar
        v-frfei-msft
        Community 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