Determining Next Date

[bblackwell3]

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

[bblackwell3]

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]

Yes, check note below my post.

[bblackwell3]

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

[dufoq3]

Hi, if my code is not what you need - provide expected result based on sample data please.

[bblackwell3]

Trying to achieve the below, with each title(application) on one row. I have logic worked out for the latest date column

[dufoq3 · Accepted solution]

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

[bblackwell3]

 Thanks again for the help. This solution works for my issue

[dufoq3]

You're welcome 😉

[bblackwell3]

Sorry, for the late follow-up. Would there be a reason for the Next Date logic not to acurately see am item with today's date as being the next date? I have a record where the "next" date is today, but the logic does not count the records? See the 8/5/2024 record below

[dufoq3]

Hi, in Ad_NextDate step replace > with >=

[dufoq3]

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