DAX sum hours for shift and date with minimum hours charge

[GCBrent]

I have an interesting one!

I have onsight date and time as well as shift name, client and total hours spent on a job. This is only if the Job uses another column which I have worked out to determine if it is chargeable so that column shows YES or NO.

Columns are Date, JobNumber, Date&Time Onsight, Date&Time Finished, Client, Shift, Chargeable, Total Hours

Here is the rub.

1. A minimum 4hr charge is applicable unless there are multiple jobs for the same client in that timeframe for that day so if 1 job was attended, and took 1hr, 4hrs would be charged.
2. If there where 3 jobs attended all within that 4hr period it would be the minimum charge.
3. If it went longer than 4hrs for that client and shift then you would use the MAX value.
4. This is per day so I am trying to work out how to do that and because of the complexity of this has gone beyond my knowledge.

I truly appreciate any input on this. I have tried quite a few ways to do this and come up with nothing useable!

[Anonymous]

Hi @GCBrent ,

Thanks for the reply from lbendlin .

Your requirement is: calculate the number of hours per person per day per shift. If it is greater than 4, take the MAX value, otherwise take 4. I don't know if my understanding is correct?

Create a calculated column to calculate the total billable hours per client, per shift, and per day, while considering the minimum fee rule.

Column =
VAR MinValue = 4
VAR TotalHoursPerClientShiftDay =
SUMX(
FILTER(
Table,
Table [Client] = EARLIER(Table [Client]) &&
Table [Shift] = EARLIER(Table [Shift]) &&
Table [Date] = EARLIER(Table [Date])
),
Table [Total Hours]
)
RETURN
MAX(TotalHoursPerClientShiftDay, MinValue)

Try this syntax.

If this doesn't help you solve the problem, please provide me with the pbix file or sample data.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GCBrent]

Hi Yang,

Any help would be appreciated. I am still struggling with how to take into account the 4 hour window from the first job for that client on that day. Your solution works better than what I had but need to take into account if a few jobs come in, from the first job essentially gives them a 4 hour window for any jobs that are also at that site for that customer are not charged for the 4 hour but are just part of the initial 4 hour minimum charge. If it goes over that 4 hours than they are charged for the total time. Does that make sense?

[GCBrent]

After I tested it while it works a lot better as seen below it still doesnt take into account the 4 hour window.

The new column is labelled Hours to Charge. For example on line 2 onsite time was 4:30pm so the customer would be covered to 8:30pm for another callout fee which line 3 would then not be charged for. Similarly on lines 4 and 5 the job on line 5 would be covered as it falls within 4 hrs of the job on line 4. If it went longer than a 4 hour window than that higher value would be charged.

Sorry for the confusion I thought I described it perfectly but not so much. Does that make sense?

[GCBrent]

Thank you so much for that! When I do try and implement it however it is coming up with and error;

I seem to be including the correct columns and tables but in the earlier function it doesnt seem to pickup the column at all and not sure why.

[GCBrent]

Apologies, I had it as a measure not a column! Long day... Testing now.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...