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 | 08:05 | 01/01/2022 | 09:12 | ||
| 002 | 01/01/2022 | 09:04 | 01/01/2022 | 10:12 | ||
| 003 | 02/01/2022 | 14:12 | 02/01/2022 | 14:14 | ||
| 004 | 08/03/2022 | 13:14 | 08/03/2022 | 13:58 | ||
| 005 | 15/04/2022 | 17:15 | 15/04/2022 | 18:01 |
As you can see I have multiple transaction per day. I need:
1- Calculate the available time per month which is the sum of the available time per day (available time per day = end time of the last transaction - start time of the first transaction). Days where there are no transactions the available time is assumed to be 8h
2- Calculate usage per month which is the sum of the usage per day which is the sum of the durations of every transaction (duration of every transaction = end time - start time
3- Calculate the usage % per month which is usage per month / available time per month
Let me know if you need any other information
5 Replies
Hi Anonymous ,
see my solution on the pbi File. Let me know if you can not open the link.
https://1drv.ms/u/s!Aj45jbu0mDVJiHRJvAMfvPPe444G?e=UCv3OP
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
For the sample data that you have shared, show the expected result.
I forgot to mention. For days where there are transactions, if available time is less than 8h it will be considered 8h. otherwise it's the result of the calculation last transaction time - first transaction time
Ultimately I want to have a table as follows :
Date Usage Available time Usage % 01/01/2022 2.25 8 28.25% 02/01/2022 0.03 8 0.375% 03/01/2022 0 8 0% Total 2.28 24 9.5% 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
