Forum Discussion
Need help
Hi everyone I need help with writing one or multiple dax formula to do the following My dataset looks like this : Operation Start date Start time End date End time 001 01/01/2022...
I figured creating a date tabel is my best option here. I will reformulate my problem as follow
My facts tables is as follows
| Transaction # | Start Date | Start time | End Date | End time | Duration |
| 000001 | 01/01/2022 | 08:13 | 01/01/2022 | 09:05 | 0.87 |
| 000002 | 01/01/2022 | 16:28 | 01/01/2022 | 17:25 | 0.95 |
| 000003 | 03/01/2022 | 09:15 | 02/01/2022 | 10:02 | 0.78 |
My output date table will be as follows
| Date | Usage | Available time | Usage % |
| 01/01/2022 | 1.82 | 16 (8<9.2<16) | 11.38% |
| 02/01/2022 | 0.78 | 8 (0.78<8) | 9.75% |
- Usage is the sum of durations (end time - start time) of each transaction
- Available time is end time of last transaction - start time of first transaction. We have 3 scenarios for this:
If it's less than 8h it will be 8h
if it's more than 8h but less than 16h it will be 16h
If it's more than 16h it will be the value found
Note: Available time on saturdays and sundays will be 0 if it's less than 8h or the value found if it's more than 0
- Usage % is usage/available time
I would like to show the totals by month also
Thank you