Excel formula to power BI

[shado26]

Hi Foks,

can any one help me to covert the below excel formula to power BI DAX 

=((NETWORKDAYS.INTL(received  time ,approved time ,7)-1)*(working hours end - working hours start)

+IF(NETWORKDAYS.INTL(approved time ,approved time ,7),

MEDIAN(MOD(approved time,1 ),working hours end ,working hours start),working hours end )

-MEDIAN(NETWORKDAYS.INTL(received  time,received  time,7)

*MOD(received  time,1),working hours end ,working hours start))

[v-xjiin-msft · Accepted solution]

@shado26

In your scenario, your excel formula is used to format the time difference to days, hours, minutes and seconds. Right?

In Power BI, you can try following method:

1. Go to Edit Queries -> add a custom column with Power Query expression like:

= Duration.ToText([end]-[start])

Or: 

= Duration.ToRecord([end]-[start])

And expand

2. Then Close & Apply, if you use Duration.ToRecord simply concatenate all the columns in a calculated column:

SLA =
Table4[DurationToRecord.Days] & " days "
    & Table4[DurationToRecord.Hours]
    & " hours "
    & Table4[DurationToRecord.Minutes]
    & " minutes "
    & Table4[DurationToRecord.Seconds]
    & " seconds "

Thanks,
Xi Jin.

[v-xjiin-msft]

@shado26

Could you please share us some sample data which can help us make some tests and get the right direction?

Thanks,
Xi Jin.

[shado26]

Hi @v-xjiin-msft

sure here you go  

SLA Test

[v-xjiin-msft · Accepted solution]

@shado26

In your scenario, your excel formula is used to format the time difference to days, hours, minutes and seconds. Right?

In Power BI, you can try following method:

1. Go to Edit Queries -> add a custom column with Power Query expression like:

= Duration.ToText([end]-[start])

Or: 

= Duration.ToRecord([end]-[start])

And expand

2. Then Close & Apply, if you use Duration.ToRecord simply concatenate all the columns in a calculated column:

SLA =
Table4[DurationToRecord.Days] & " days "
    & Table4[DurationToRecord.Hours]
    & " hours "
    & Table4[DurationToRecord.Minutes]
    & " minutes "
    & Table4[DurationToRecord.Seconds]
    & " seconds "

Thanks,
Xi Jin.

[shado26]

HI @v-xjiin-msft

sorry for the delay , the above working 

i want to inculding working hours and exclude Weekend as well

to know if this order are approved within 1 h 30 min or not 

can you assist me on this 

[v-xjiin-msft]

Hi @shado26

Sure.

However, I'm not quite understand about your requirement. What did you mean about inculding working hours and exclude Weekend and to know if this order are approved within 1 h 30 min or not?

What's your logic? Could you please make a more detailed description and share us your desired result based on your sample data? So that we'll have a right direction.

Thanks,
Xi Jin.

[shado26]

HI @v-xjiin-msft

msft let me explain what i need we are measure approval time & time of order received

this should be approved with 1h 30 min Max

moreover our working hours from 9 to 5 and friday and saturday are off

so if i received our by 4:30 PM and i approved next working day on 9:00 AM

the approval time should be 30 MIN

[v-xjiin-msft · Accepted solution]

Hi @shado26,

To calculate working hours exclude weekends. You can refer to following relevant thread:

https://community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends... 

Thanks,
Xi Jin.