The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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?
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
Trying to achieve the below, with each title(application) on one row. I have logic worked out for the latest date column
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
Thanks again for the help. This solution works for my issue
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
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