Forum Discussion
Solved
Latest entry with Conditions
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.
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".
3 Replies
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-p/963216
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/1447523Sorry 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 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".
