Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Time duration/difference in same column between different status/phase

Hi, I'm trying to get the duration between the different decision types in order to visualize the total duration time per decision type, but also total duration inbetween eg. "WB1 to WB2" and "WB2 to...
DAX
Measures
amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Why the second w1 has  under o1 has 4 days and why o3 do not have any value

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak hi, 4 days refers to the duration it has been in this particular stage/decision (decision d3) before changing to the next decision d4, therefore 4 days between decision dates.

    The reason for the empty cells is because I havent though of a good way of displaying those. Most likely it would be the total days duration before the entire opportunity is closed(status = won/lost)

    hope it makes sense.

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Anonymous , oh, I did check that diff is small date column with the next date.

       

      Try a new column 

       

      new column =
      var _min = minx(filter(table, [opportunity_id] = earlier([opportunity_id]) && [decisiondate] > earlier([decisiondate])),[decisiondate])
      return
      if(isblank(_min), blank(), datediff([decisiondate] ,_min, day))

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        amitchandak I tried the code however several functions are not supported (minx, filter, earlier) since the report is using DirectQuery as connection type... is there any alternative to the code above which will be supported with DQ?

        Thanks.