How to calculate resolution total time using DAX in hour?-CodePudding

I have a formula in Excel to calculate total time between start and end date. Here is the formula

=IF(M2="","",IF(((M2-L2)*24-12<=12,12,(M2-L2)*24-12))

I would like to use it in PowerBI using DAX formula.

I tried this

column = HOUR('Logs'[Finish Date]-HOUR('Logs'[Start Date]))*24-12

But the value is different with excel calculation. Anyone can give idea please? I am really new with DAX.

Thank you so much

CodePudding user response：

If you have datetime data type for your column "Start Date" and "End Date", in transformation layer you can simply add a new column as below and this will return your expected output.

You can still create measures in the report level as below-

hour_diff = DATEDIFF(min(time_diff[Start Date]),min(time_diff[Finish Date]),HOUR)

Output will be same as below-

You can check this blog for more examples.

CodePudding user response：

column =   
    SWITCH(
        TRUE()
        ,IF ([start]=BLANK()||[end]=BLANK(),TRUE(),FALSE()),BLANK()
        ,DATEDIFF([start],[end],HOUR)<12,12
        ,DATEDIFF([start],[end],HOUR)
    )

Second solution:

column = 
    IF(
        [start]=BLANK()||[end]=BLANK()
        ,BLANK()
        ,DATEDIFF([start],[end],HOURS)*24-12
    )