Count/Sum Days between 2 set of Dates

[Anonymous]

Hi Everyone, I'm fairly new to Power BI and DAX. But I need my formula from Excel to be in DAX/Power BI within the next few days:

=SUM(DAYS360(A3,F3),-IF(AND(B3<F3,C3<F3),DAYS360(B3,C3),IF(AND(B3<F3,C3>=F3),DAYS360(B3,F3),0)))

And here's the sample Tables:

What I basically trying to calculate in PowerBI, for example is Time To Submit, the number of Days Between Open Date and Submit but exclude the days within Hold Start and Hold End in the calculations.

Appreciate help on this. Thanks! 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

You can try this measure

Measure = 
var _v1=DATEDIFF(MIN('Table'[Open Date]),MIN('Table'[Submit]),DAY)  //DAYS360(A3,F3)
var _v2=IF(AND(MIN('Table'[Hold Start])<MIN('Table'[Submit]),MIN('Table'[Hold End])<MIN('Table'[Submit])),DATEDIFF(MIN('Table'[Hold Start]),MIN('Table'[Hold End]),DAY),IF(AND(MIN('Table'[Hold Start])<MIN('Table'[Submit]),MIN('Table'[Hold End])>=MIN('Table'[Submit])),DATEDIFF(MIN('Table'[Hold Start]),MIN('Table'[Submit]),DAY),0))  //IF(AND(B3<F3,C3<F3),DAYS360(B3,C3),IF(AND(B3<F3,C3>=F3),DAYS360(B3,F3),0))
return _v1-_v2 

result

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Thank you so much!! 😊