Getting Datediff between date time , Getting Average and Getting of KPI ( Miss and Hit )

[syaiful_fcpc]

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 · Accepted solution]

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.

[v-weiyan1-msft · Accepted solution]

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.

[syaiful_fcpc · Accepted solution]

Thanks for solution