Forum Discussion

syaiful_fcpc's avatar
syaiful_fcpc
Frequent Visitor
2 years ago
Solved

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

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 NUMBERX.DELIVERYDATEX.ACTDELIVTRUCKINGX.ATACCSTARTDATEX.CCFINISHDATE
HTM202207050083815/07/2022 20.0015/07/2022 20.0015/07/2022 20.3415/07/2022 11.10
HTM202207050391816/07/2022 14.0016/07/2022 14.0015/07/2022 08.4415/07/2022 10.35
HTM202207060096418/07/2022 20.0018/07/2022 20.0015/07/2022 08.4415/07/2022 10.30
HTM202207080018322/07/2022 10.4822/07/2022 10.4821/07/2022 21.2121/07/2022 17.35
HTM202207250084008/08/2022 21.1608/08/2022 21.1604/08/2022 04.5604/08/2022 09.48
HTM202207290140813/08/2022 11.0013/08/2022 11.0012/08/2022 03.2911/08/2022 10.48
HTM202207280157412/08/2022 15.0012/08/2022 15.0012/08/2022 03.2912/08/2022 14.08
HTM202208020070416/08/2022 20.0016/08/2022 20.0016/08/2022 13.4316/08/2022 13.46
HTM202208190157530/08/2022 14.3330/08/2022 14.3329/08/2022 15.1629/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

Need Help
Show and Tell
Tips and Tricks
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    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.

2 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Community Support
    2 years ago

    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.