Solved: Getting Datediff between date time , Getting Avera...

syaiful_fcpc · ‎01-06-2024

Dear Powerbi Master

Im new in this things, i have some task which i have some doubt to do analytic.
here , i have table (attached),

HTM NUMBER	X.DELIVERYDATE	X.ACTDELIVTRUCKING	X.ATACCSTARTDATE	X.CCFINISHDATE
HTM2022070500838	15/07/2022 20.00	15/07/2022 20.00	15/07/2022 20.34	15/07/2022 11.10
HTM2022070503918	16/07/2022 14.00	16/07/2022 14.00	15/07/2022 08.44	15/07/2022 10.35
HTM2022070600964	18/07/2022 20.00	18/07/2022 20.00	15/07/2022 08.44	15/07/2022 10.30
HTM2022070800183	22/07/2022 10.48	22/07/2022 10.48	21/07/2022 21.21	21/07/2022 17.35
HTM2022072500840	08/08/2022 21.16	08/08/2022 21.16	04/08/2022 04.56	04/08/2022 09.48
HTM2022072901408	13/08/2022 11.00	13/08/2022 11.00	12/08/2022 03.29	11/08/2022 10.48
HTM2022072801574	12/08/2022 15.00	12/08/2022 15.00	12/08/2022 03.29	12/08/2022 14.08
HTM2022080200704	16/08/2022 20.00	16/08/2022 20.00	16/08/2022 13.43	16/08/2022 13.46
HTM2022081901575	30/08/2022 14.33	30/08/2022 14.33	29/08/2022 15.16	29/08/2022 13.48

My target is
1. Create a column (CCWH) to get Datediff betwen X.ACTDELIVTRUCKING and X.CCFINISHDATE , result will be in hour

2. Create a column (ATACC) to get Datediff betwen X.CCFINISHDATE and X.ATACCSTARTDATE, result will be in hour
3. Getting Average Hour point 1
4. Getting Average Hour point 2
5. Create a Column or measure to get status of average each month from column (X.DELIVERYDATE), if point 3 < 2 then Hit, else Miss
6. Create a Column or measure to get status of average each month from column (X.DELIVERYDATE), if point 4 < 2 then Hit, else Miss
7. Point 3 + Point 4
8. Create a column or measure to get status of point 7 for each month from column (X.DELIVERYDATE), if point 3 < 2 then Hit, else Miss

kindly Provide a pbix file, so i can learn from it.

Thanks and will be appreciates any help

Syaiful

v-weiyan1-msft · ‎01-09-2024

Hi @syaiful_fcpc ,

Based on the sample and description you provided, Please try the following steps:

Target1. Create a column (CCWH) to get Datediff.

CCWH = DATEDIFF('Table'[X.CCFINISHDATE],'Table'[X.ACTDELIVTRUCKING],HOUR)

Target 2. Create a column (ATACC) to get Datediff.

ATACC = DATEDIFF('Table'[X.ATACCSTARTDATE],'Table'[X.CCFINISHDATE],HOUR)

Target 3. Getting Average Hour point 1.

Avg_CCWH = AVERAGE('Table'[CCWH])

Target 4. Getting Average Hour point 2.

Avg_ATACC = AVERAGE('Table'[ATACC])

Target 5.

Measure1 = IF(SELECTEDVALUE('Table'[Avg_CCWH]) < MAX('Table'[ATACC]),
             "Hit",
             "Miss")

Target 6.

Measure2 = IF(SELECTEDVALUE('Table'[Avg_ATACC]) < MAX('Table'[ATACC]),
             "Hit",
             "Miss")

Target 7.

Avg_Total = 'Table'[Avg_CCWH] + 'Table'[Avg_ATACC]

Result is as below.

For further details，please find attachment.
From your description, I noticed that your Target8 and the previous Target has some similarities.
Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

syaiful_fcpc · ‎01-09-2024

Thanks for solution