Forum Discussion

Jolyon's avatar
Jolyon
Helper III
9 years ago
Solved

Calculation with date/hours?

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!

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    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's avatar
      Jolyon
      Helper III
      9 years ago

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