Latest entry with Conditions

[Laddyboy0021]

Hello,

I have been struggling with this for a bit now.  I am looking for a why to look at some audit information to find out the last time a record was submitted.  I want to group the data on the TIme_Sheet_RecID, find all the Audit_Text that starts with "Submit to Approver" and give me back that a true on the row that is the oldest record based on the Date_Audit_UTC time.

I have tried this as a column but doesn't quite work:

Column = if(

    v_rpt_TimeSheetAudit[Date_Audit_UTC].[Date] = CALCULATE(

        max(v_rpt_TimeSheetAudit[Date_Audit_UTC].[Date]),

        FILTER(

            v_rpt_TimeSheetAudit,

            v_rpt_TimeSheetAudit[Time_Sheet_RecID] = EARLIER(v_rpt_TimeSheetAudit[Time_Sheet_RecID]) &&

            LEFT(EARLIER(v_rpt_TimeSheetAudit[Audit_Text]),9) = "Submit to"

        )),

        "TRUE",

        "FALSE"

    )

Any help would be appreciated.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Laddyboy0021]

Sorry for not including this before.  Below is some sample data that I am working with.  The far right column is an example of what I would like to see as the results.  There has been a slight change of what I need to get.  I need to get the first time a Time sheet (Time_Sheet_RecID) has an aduit text starting with "Submit to Approver".  Then I need to grab the time from the Date_Audit_UTC and supply that as the result.

Time_Audit_RecID	Time_Sheet_RecID	Member_Identifier	Member_Name	Submitted_By	Date_Start	Date_End	Date_Audit_UTC	Audit_Text	Time_Zone	Date _Submitted
1642332	7575	UserA	User A	User A	26-Jun-22	2-Jul-22	7/1/2022 21:48	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/5/2022 15:41
1642333	7575	UserA	User A	User A	26-Jun-22	2-Jul-22	7/1/2022 21:48	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	7/5/2022 15:41
1644368	7575	UserA	User A	User B	26-Jun-22	2-Jul-22	7/5/2022 14:25	Tier 1 has rejected: Please updated rejected time entries to replect work done. There are several hours in here that belong in client tickets. See notes on each rejected time entry.	Eastern Standard Time	7/5/2022 15:41
1645133	7575	UserA	User A	User A	26-Jun-22	2-Jul-22	7/5/2022 15:41	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/5/2022 15:41
1645134	7575	UserA	User A	User A	26-Jun-22	2-Jul-22	7/5/2022 15:41	Submit to Approver / Header Status Changed / Old Status - Rejected / New Status - Errors Corrected	Eastern Standard Time	7/5/2022 15:41
1648721	7575	UserA	User A	User B	26-Jun-22	2-Jul-22	7/7/2022 12:33	Tier 1 has approved	Eastern Standard Time	7/5/2022 15:41
1648732	7575	UserA	User A	User B	26-Jun-22	2-Jul-22	7/7/2022 12:33	Time Sheet changed from Errors Corrected to Approved by Tier 1	Eastern Standard Time	7/5/2022 15:41
1652468	7644	UserA	User A	User A	3-Jul-22	9-Jul-22	7/8/2022 21:54	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/8/2022 21:54
1652469	7644	UserA	User A	User A	3-Jul-22	9-Jul-22	7/8/2022 21:54	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	7/8/2022 21:54
1653303	7644	UserA	User A	User B	3-Jul-22	9-Jul-22	7/11/2022 12:41	Tier 1 has approved	Eastern Standard Time	7/8/2022 21:54
1653316	7644	UserA	User A	User B	3-Jul-22	9-Jul-22	7/11/2022 12:41	Time Sheet changed from Pending Approval to Approved by Tier 1	Eastern Standard Time	7/8/2022 21:54
1662548	7703	UserA	User A	User A	10-Jul-22	16-Jul-22	7/15/2022 22:57	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/15/2022 22:57
1662549	7703	UserA	User A	User A	10-Jul-22	16-Jul-22	7/15/2022 22:57	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	7/15/2022 22:57
1666865	7703	UserA	User A	User B	10-Jul-22	16-Jul-22	7/18/2022 21:47	Tier 1 has approved	Eastern Standard Time	7/15/2022 22:57
1666890	7703	UserA	User A	User B	10-Jul-22	16-Jul-22	7/18/2022 21:47	Time Sheet changed from Pending Approval to Approved by Tier 1	Eastern Standard Time	7/15/2022 22:57
1675638	7726	UserA	User A	User A	17-Jul-22	23-Jul-22	7/23/2022 17:03	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/23/2022 17:03
1675639	7726	UserA	User A	User A	17-Jul-22	23-Jul-22	7/23/2022 17:03	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	7/23/2022 17:03
1676380	7726	UserA	User A	User B	17-Jul-22	23-Jul-22	7/25/2022 12:22	Tier 1 has approved	Eastern Standard Time	7/23/2022 17:03
1676399	7726	UserA	User A	User B	17-Jul-22	23-Jul-22	7/25/2022 12:22	Time Sheet changed from Pending Approval to Approved by Tier 1	Eastern Standard Time	7/23/2022 17:03
1687951	7765	UserA	User A	User A	24-Jul-22	30-Jul-22	7/29/2022 23:23	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	7/29/2022 23:23
1687952	7765	UserA	User A	User A	24-Jul-22	30-Jul-22	7/29/2022 23:23	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	7/29/2022 23:23
1688311	7765	UserA	User A	User B	24-Jul-22	30-Jul-22	8/1/2022 12:03	Tier 1 has approved	Eastern Standard Time	7/29/2022 23:23
1688320	7765	UserA	User A	User B	24-Jul-22	30-Jul-22	8/1/2022 12:03	Time Sheet changed from Pending Approval to Approved by Tier 1	Eastern Standard Time	7/29/2022 23:23
1697386	7810	UserA	User A	User A	31-Jul-22	6-Aug-22	8/5/2022 21:22	User selected Yes in response to confirmation message, "By clicking YES, you are confirming that you have reviewed this time sheet report and that it is correct to the best of your knowledge."	Eastern Standard Time	8/5/2022 21:22
1697387	7810	UserA	User A	User A	31-Jul-22	6-Aug-22	8/5/2022 21:22	Submit to Approver / Header Status Changed / Old Status - Open / New Status - Pending Approval	Eastern Standard Time	8/5/2022 21:22
1698288	7810	UserA	User A	User B	31-Jul-22	6-Aug-22	8/8/2022 12:14	Tier 1 has approved	Eastern Standard Time	8/5/2022 21:22
1698294	7810	UserA	User A	User B	31-Jul-22	6-Aug-22	8/8/2022 12:14	Time Sheet changed from Pending Approval to Approved by Tier 1	Eastern Standard Time	8/5/2022 21:22

[lbendlin · Accepted solution]

Here is the query. Note that the date for the first item disagrees.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Zdbb6JQFIX/yo7PXjiHO2+1aTKZh2kzdh6atg8ou8KIYOBY47+fDYiIgxAsxsQmTSSHsl3fOmsBvr72mKZwWea9fk9XdZU+/sQY3e0+oXTAtcHPdTDgyT9zOvSzQ33ERlziHDizFCO/IEYfZwIdeMEYvAAijFdhECOIEGZh8OFFS1t4YQBLjGN7jn146423MPO92cIL5vDyMOnDNlyDHWF+QbIuXFuk6679iTT108MNfYtwvRiEt0SIXURBJ1ZhJMAOnOwKj/5imhNFpCrRIFyEKcYCwo9kXASLINz46Mxx+NYjiAc7FhgFMBE0w44ceKbhKa2a0TLVUljvvZ9bKHdp4WQ9XXqpzrvVKgo/6fIR/EDboQNSJNYx3Lt2MCfyETz6Tr44gMcVBrT2CzfF2hMGTuJdNsv2W+MpsmY04Y3r8PKhisWTCc8eXcBoC2PaqL9pTix48tGmfKxXjp3kJj+R7SoGIvIoSeQIbW0SLdiE0QKcMMAhPLtIKUmSEiN5Zfvg0pamuUvPpBGYoh+SCbRGIaN5NHi2QBEPYYIIQShoPOUR7Zlb8eXbYWvXVPbFUJSGfstekYVKlxae36vfeSKOuvUQRSEl7T5zAJ3WiIbO2Ze6pe+GciuN20G37IzxHE3NT4R2mihAkzRAs529H1G4/M+8g41xYLqFjKWtfJUr2e2Kbly1qZELyeahemN/M1bzATfcuxJt4aDZoYNXfJxV0cmyJDfRjWvoGNvHO72rtK1ctSamdaupunLH5p1RuQr5GlfT9xZdT509HRgmFaKZVgLYNZlzS9Vvv3Vl3MJFs1sXr9i8SkLN0NQmwnE9YRE/RT+jfCdkmVLXsi7WvyoCXdXkrIBcq4+OXqjm8iEBl3d3EN3au3DDBSzjFi6a3bp4xQJWElJMpCbCcT2hun/SpCttC3hCltlofFtZFytgFYGhm2r6Hq9rDT9VlEK1LJUIzF2vZYt/hwKWcAsXebcuXrOAVYSGzBpzMq4hNEbFm550zg++E6q41LGqy9WvAsDUZSN9fzaYVBscmRWitcHdep7rV/fP73Thpst3RFtYqHdp4dWaV41ncMNowhvX4Rn7hDOlde9OaTKVbjVdqHXH8t//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time_Audit_RecID = _t, Time_Sheet_RecID = _t, Member_Identifier = _t, Member_Name = _t, Submitted_By = _t, Date_Start = _t, Date_End = _t, Date_Audit_UTC = _t, Audit_Text = _t, Time_Zone = _t, #"Date _Submitted" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Time_Sheet_RecID"}, {{"Rows", each _, type table [Time_Audit_RecID=nullable text, Time_Sheet_RecID=nullable text, Member_Identifier=nullable text, Member_Name=nullable text, Submitted_By=nullable text, Date_Start=nullable text, Date_End=nullable text, Date_Audit_UTC=nullable text, Audit_Text=nullable text, Time_Zone=nullable text, Date _Submitted=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort(Table.SelectRows([Rows], each Text.StartsWith([Audit_Text], "Submit to Approver")),{{"Date_Audit_UTC", Order.Ascending}})[Date_Audit_UTC]{0}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"}, {"Time_Audit_RecID", "Member_Identifier", "Member_Name", "Submitted_By", "Date_Start", "Date_End", "Date_Audit_UTC", "Audit_Text", "Time_Zone", "Date _Submitted"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".