Forum Discussion

dejadpower's avatar
dejadpower
Helper I
2 years ago

finding Date Difference grouped

Hello there,

 

My sample data is

desired output is :

 

 

i could achieve the earliest start and latest end. But couldn't achieve Date difference is as 6 .

the measure i tried takes entire date difference between jan 1 to dec 31 .  Anybody please guide .

thank you

 

 

Need Help
Tips and Tricks

7 Replies

  • JamesFR06's avatar
    JamesFR06
    Resolver IV
    2 years ago

    HI

    Earliest_Start=calculate(min(Start Time), all(tablewithdata))

    Lastest_End= calculate(max(Start Time), all(tablewithdata))

    Days=[Lastest_End]-[Earliest_Start]

    • dejadpower's avatar
      dejadpower
      Helper I
      2 years ago

      Thank you JamesFR06 

      This method, i have already tried. it will take difference of Jan 04 to Dec 29 - which is 360 days

      the expected output is 04/01 to 06/01 + 29/12 to 31/12  , which is 6 days in total

      the second row difference should be skipped, because it is already covered in (29/12 to 31/12)

      the 3rd , 4th , 5th rows difference should be skipped, because it is already covered in (04/01 to 06/01)

      instead of taking all dates, it should cover the existing from , to date difference only.

      Please guide

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dejadpower ,

     

    Thanks JamesFR06  for the quick reply and solution. Here are some of my additions:

     

    You can use the DATEDIFF function to calculate the number of days between measures.

    Days = DATEDIFF([Earliest_Start],[Lastest_End],DAY)

    Best Regards,

    Neeko Tang

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

    • dejadpower's avatar
      dejadpower
      Helper I
      2 years ago

      Hi Anonymous  , Thanks for helping.

       

      for data:

      Earliest Start : 04/01/2023

      Latest End : 12/31/2023

       

      The formula

      DATEDIFF([Earliest_Start],[Lastest_End],DAY)

      will give 360 days

      But the actual working days of resources are 6 days.

      kindly help to ahieve this

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi dejadpower ,

         

        Click "transform data" to enter the power query editor to copy a table, and then open the "Advanced Editor" to copy and paste the following code.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VMFTSUTKy1Dc00jcyMDIGcowN4ZxYHSSFRugKkTgoCkFyBib6BoYwhUgcDBOJUmiCodAMoTAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Start Time.1", "Start Time.2", "Start Time.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource", type text}, {"Start Time.1", Int64.Type}, {"Start Time.2", Int64.Type}, {"Start Time.3", Int64.Type}, {"End Time", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Start Time.3", type text}, {"Start Time.2", type text}, {"Start Time.1", type text}}, "en-US"),{"Start Time.3", "Start Time.2", "Start Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Start Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type date}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "End Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"End Time.1", "End Time.2", "End Time.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"End Time.1", Int64.Type}, {"End Time.2", Int64.Type}, {"End Time.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"End Time.3", type text}, {"End Time.2", type text}, {"End Time.1", type text}}, "en-US"),{"End Time.3", "End Time.2", "End Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Merged", "End Time"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "end time 2", each Date.AddDays([Start Time],3)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Start Time", "end time 2"}, {{"Table",each _}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Duration.Days(List.Max([Table][#"End Time"])-
List.Min([Table][#"Start Time"]))+1),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"end time 2", type date}, {"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Table"})
in
    #"Removed Columns"

        Then we get two tables.

        Click "Close and Apply".

        Then we can create measures.

        Earliest_Start = CALCULATE(MIN('Table (2)'[Start Time]),ALL('Table (2)'))
        Lastest_End = CALCULATE(MAX('Table (2)'[End Time]), ALL('Table (2)'))
        Days = CALCULATE(SUM('Table'[Custom]),ALL('Table'))

        Best Regards,

        Neeko Tang

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