Calculation with date/hours?

[Jolyon]

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!

[Eric_Zhang · Accepted solution]

@Jolyon

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.

[Jolyon]

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;)

[Anonymous]

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.