Need help

[Anonymous]

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 

[Anonymous]

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 

[mangaus1111]

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.

[Anonymous]

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%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

For the sample data that you have shared, show the expected result.