The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello dear community,
I kindly ask for your help.
I have a table with different art of orders and their Open and Closed Date(in format dd: mm: yyyy hh:mm:ss).
1)Now I want to calculate the time between Closed and Open Date, preferably in Hours.
i.e. the logic would be: if "Closed Date" is not Null, then [Closed Date]-[Open Date] else Null.
I tried to create new column and calculate [Closed Date]-[Open Date], but it gives the result as Days, or I can transform it to format hh:mm:ss then it takes only the hours into consideration, but not the Date/days.
What I need is 24; 34 or 245 (hours) for Example.
Next challenge here is this condition: if "Closed Date" is not Null.. If I don't take it, the formula calculates for null values [Null]-[Open Date] and gives me naturally wrong data.
How can I take the condition in the calculation?
2) As a next step I'd like to build a distribution diagram to show the number of orders allocated by hours: less then 12 hours, less then 36 hours etc.
I guess I could add here a customized column with DAX, like:
if [Time between] <= 12 then "12";
else if [Time between] > 12 && <= 36 then "36";
else if…
How could I handle these multiple "if" conditions in DAX? Or is there some other way to define distribution values?
Thanks a lot for help and advice!
Solved! Go to Solution.
Please check a formula as below.
Hours Between = VAR elapsedHours = DATEDIFF ( YourTable[Open Date], YourTable[Closed Date], HOUR ) RETURN IF ( ISBLANK ( YourTable[Closed Date] ), BLANK (), SWITCH ( TRUE (), elapsedHours <= 12, "12", elapsedHours <= 24, "24", elapsedHours <= 36, "36", elapsedHours <= 48, "48", elapsedHours <= 72, "72", elapsedHours <= 240, "240", "Over 240" ) )
If it answers your question, please accept it as solution to close this thread. For any question, feel free to post.
Please check a formula as below.
Hours Between = VAR elapsedHours = DATEDIFF ( YourTable[Open Date], YourTable[Closed Date], HOUR ) RETURN IF ( ISBLANK ( YourTable[Closed Date] ), BLANK (), SWITCH ( TRUE (), elapsedHours <= 12, "12", elapsedHours <= 24, "24", elapsedHours <= 36, "36", elapsedHours <= 48, "48", elapsedHours <= 72, "72", elapsedHours <= 240, "240", "Over 240" ) )
If it answers your question, please accept it as solution to close this thread. For any question, feel free to post.
thank you all kindly!!
I calculated the Time between = ROUND((24, * (Table1[Closed Date]-Table1[Open Date]));1)
and then used SWITCH loop.
But your solution is of course more exquisite;)
For difference in dates in hours, try playing with the below logic:
Var = FORMAT( [Closed Date] - Table1[Open Date], "DD" ) * 24 + FORMAT( [Closed Date] - Table1[Open Date], "hh" )
As for Banding, try playing with the below approach:
=
SWITCH (
TRUE (),
[Time between] < 12, "<12",
[Time between] >= 12 && [Time between] <= 24, "12-> 24",
"24+"
)
The switch statement can go on forever so put in as many bands as you'd like!
There is a better approach than this by levaerging a banding table by the way. It is quite an advanced technique and can cause some issues with relationships unless you're comfortable with data modelling. If you're interested I can post details.