Forum Discussion

bblackwell3's avatar
bblackwell3
Helper II
2 years ago
Solved

Determining Next Date

Hey All. I have a dataset with an Application name column and a, Upcoming dates column with dates like below:

Some applications may have one date in the Upcoming dates column or multiple dates separated by commas. I am tasked with determining which date is the next date, but not the latest date in each row ( I have devised dax for the latest date). Then count how many applications fall into the next date.

So for the above, count Strategicflight because the next calendar date is 8/1/2024 but do not count Erwin because it is not 8/1/2024
Any help would be great

 

Thanks

Need Help
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSixJTc9MdsvJTM8oUdJRMtU3NNI3MjAy0VGw0DcwhDMNTcFMpVidaCXXovLMPBOgYgt9c4RoSGoxyAADc4Q2EBumLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Upcoming Dates" = _t]),
    Ad_NextDate = Table.AddColumn(Source, "Next Date", each 
        [ a = List.Transform(Text.Split([Upcoming Dates], ","), (x)=> Date.From(x, "en-US")),
          b = List.Select(a, (x)=> x > Date.From(DateTime.FixedLocalNow())),
          c = List.Min(b)
        ][c], type date)
in
    Ad_NextDate

11 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi bblackwell3, 

     

    Result

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSixJTc9MdsvJTM8oUdJRMtU3NNI3MjAy0VGw0DcwhDMNTcFMpVidaCXXovLMPBOgYgt9c6hoLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, #"Upcoming Dates" = _t]),
    Ad_Dates = Table.AddColumn(Source, "Dates", each List.Transform(Text.Split([Upcoming Dates], ","), (x)=> Date.From(x, "en-US")), type list),
    NextDate = List.First(List.Sort(List.Select(List.Combine(Ad_Dates[Dates]), each _ > Date.From(DateTime.FixedLocalNow())))),
    Tbl = [ a = Table.SelectRows(Ad_Dates, (x)=> List.Contains(x[Dates], NextDate)),
    b = #table(type table[Next Date=text, Next Date App Count=Int64.Type, Applications=text], {{ NextDate, Table.RowCount(a), Text.Combine(a[Application], ", ") }})
  ][b]
in
    Tbl

     

  • bblackwell3's avatar
    bblackwell3
    Helper II
    2 years ago

    Thanks for the response. If I follow correctly, I should be able to modify the M code above with the SharePoint source of my data?

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Yes, check note below my post.

      • bblackwell3's avatar
        bblackwell3
        Helper II
        2 years ago

        Thanks! This is very close to what I am needing. However, I do not want to combine the Applications

        into one row. Each Application should have it's own row

         

        Text.Combine(a[Application], ", ") }})

          Thanks! This is close to what I am needing. I just don't need the applications combined on one row. Each application should have it's own row