Hi Michael,

Welcome to our forum! There are some assumptions missing from the information provided. The Job Compliance table has dates and time in one column and the Scheduled Job Time table only contains times. In the attached file I've assumed it doesn't matter if the job start date is more than 1 day prior. 

You'll see in Power Query I've split the time out from the Date/Time end_time and then merged it with the Scheduled Job Timings table. This is an Excel file for simplicity, but the steps are the same in Power BI.

Mynda

Hey Mynda,

Thanks for the invite and making me feel welcome. You are right! The date plays an important role in the end_time column. I should have name end_time as job_time. As there are several entries in the job_compliance table for the same job, my intention was to get the latest job "ended" record for the current date and check compliancy. For eg, when I am running the report for today (14 Mar), job ABC has 2 "jobs" in the job_compliancy table. I only want the latest "ended" job (end_time=14/3/2021 7:02:00 PM, key=1234567). Then check the compliancy. The jobs MNO, PQR, and STU will not be counted / displayed in today's dashboard. 

I have attached the modified job_compliancy excel for your reference. I am learning on the job and am picking up PowerBI fundamentals 🙂

Hi Michael,

Please see example file attached.

Note: I used this technique to Number Grouped Data in Power Query. And I added Table.Buffer to the Filtered Rows1 step to prevent Power Query losing the sort order.

Hope that helps.

Mynda

hi Mynda,

It really helps. Just found out about another scenario though. Some of the jobs are schedule to also run on other parts of the day, shown in red as follows,

How do we cater to such scenario? tx.

I don't know. You need to tell me what the rules/logic would be.

Also, the examples above don't correlate to any of the 'execution ended' times in your original data, so right now they would simply be ignored. Perhaps you can provide a sample file that contains all scenarios and the desired result. Please just provide one file with one sheet containing the two tables and example of your desired result so I can see it all in one place and don't have to keep recreating a file from what you send.

Thanks,

Mynda