Forum Discussion
Working out Days since based on a flag
The logic for this one has me stumped and I am hoping the super brain collective may be able to help me. I have been using Power BI on and off for quite a while, but still have a long way to go and a lot to learn.
Ok so I have a data source that is prestart checks for vehicles. I have thousands of these across around 100 vehicles. I want to be able to present the Registration number, Issue and Days since first reported for each vehicle that currently has an issue.
Here is a dirty sample I created. In this example I would want to show Rego 123abc, Issue 1, DATEDIFF(today, 18/03/2024).
If it was only one vehicle I could easily sort by date (descending) and then find the first entry where Issue 1 = false. However there are a lot of vehicles so it needs to be more dynamic.
I am sure some of you have hit similiar issues and I just need a nudge in the right direction on how to accomplish this please.
Thank you,
Hi Wayne74, I'm not sure if this is what are you looking for:
Before
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxU0lEyNtA3MNY3MjAyAXJCgkJdlWJ1oFJGlrilcOsytECScnP0CUaWM8cjh9tIU6y6jJKgukwxdIGlLHDKGCPJoJuHoSkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, Date = _t, #"Issue 1" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Issue 1", type logical}}), GroupedRows = Table.Group(ChangedType, {"RegNo"}, {{"Days since last FALSE ISSUE", each [ a = List.Max(Table.SelectRows(_, (x)=> x[#"Issue 1"] = false)[Date]), //Max FALSE Date b = Duration.TotalDays(Date.From(DateTime.FixedLocalNow()) - a) ][b], Int64.Type}}) in GroupedRows
3 Replies
Hi Wayne74, I'm not sure if this is what are you looking for:
Before
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMkxU0lEyNtA3MNY3MjAyAXJCgkJdlWJ1oFJGlrilcOsytECScnP0CUaWM8cjh9tIU6y6jJKgukwxdIGlLHDKGCPJoJuHoSkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RegNo = _t, Date = _t, #"Issue 1" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Issue 1", type logical}}), GroupedRows = Table.Group(ChangedType, {"RegNo"}, {{"Days since last FALSE ISSUE", each [ a = List.Max(Table.SelectRows(_, (x)=> x[#"Issue 1"] = false)[Date]), //Max FALSE Date b = Duration.TotalDays(Date.From(DateTime.FixedLocalNow()) - a) ][b], Int64.Type}}) in GroupedRowsThat will help a lot. Thank you very much
You're welcome.
