How to calculate HOUR values 30min before and after the deadline

[magus]

Hello Dear Power BI Friends 🙂

Here is the deal: I am calculating the performance of the students depending on whether they are on time at every lesson.

There are few elements that must be considered:

1. The date and hour when the lesson is (each class has different hour start of course)

2. Every student that arrived 30 minutes before the class start is ON TIME

3. Every student that arrived 30 minutes after the class start is ON TIME

4. Otherwise, the student is considered NOT ON TIME.

I have calculated the difference between the Class time start and the hour when the student arrives - it is the Column [Arrival vs. Class start] which you will see in below formula. I have this column in below format in DAX: -0.025393519 and in fllowing format in M CODE: -00:36:34 (in this example the student arrived 36min before, so he should be considered NOT on time).

Half of hour in M CODE woul would be 0.00034 if I am not wrong.

SO, I am trying now create a new column with ON TIME and NOT ON TIME values. All the students who arrive not later than 30min after class start, and not with more advance than 30min before the class start, should be ON TIME. The others sould be NOT ON TIME.

I wrote below formula in DAX, but it does not work: 

= IF(AND('Source Data'[Arrival vs. Class start]> -0.00034, 'Source Data'[Arrival vs. Class start]< 0.00034), "On Time", "Not On Time")

Could you guide me how to do it correctly?

Best regards,

Magus

[Icey]

Hi @magus ,

What's the structure of your data model?

Could you share me a dummy .pbix file to help you better? Please remove sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[speedramps]

Hi Magnus

If you need to do it in M code then please consider this solution.

Merge the 2 staging tables to create a query  with:-

• Student
• Class
• Schedule in datetime dd/mm/yyyy hh:mm format
• Arrival in date/time dd/mm/yyyy hh:mm format

Add a custom column

• Difference = Schedule – Arrival
• Then transform Difference to Minutes
• Then add a conditional column with if < or > logic

Note, you will need handle students who didn’t attend
or students who attended when there was no class.

Hope that helps?

[Greg_Deckler]

Having a hard time visualizing this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.