Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Thank you so much!! 😊
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |