Forum Discussion

Dimitry1's avatar
Dimitry1
Frequent Visitor
3 years ago
Solved

Days between two events

Hello everyone!

 

I want to calculate how many days of fishing it takes to ship to get full before going to discharge. So I need a suggestion how could I calculate "Fishing" days until "Discharging" comes as in attached picture.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Dimitry1 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Flag =
var _fish=MAXX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])+1),[Fishing])
return
IF(
    _fish<>[Fishing],1,0)
    Group =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]<=EARLIER('Table'[Date])),[Flag])
    Day =
var _min=
MINX(
    FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Date])
var _max=
MAXX(
    FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Date])
return
DATEDIFF(
    _min,_max,DAY)+1
    All =
[Day] &" "&"days Fishing -> Discharge"

    2. Create calculated table.

    Table 2 =
var _table=
FILTER(
    'Table',MOD([Group],2)<>0)
var _table1=
SUMMARIZE(
    _table,[Day])
return
SUMMARIZE(
    _table,[All],"Avg",AVERAGEX(_table1,[Day])
)

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi Dimitry1 

     

    It's not clear to me what you are asking for.

     

    Do you want to count the number of times Fishing appears in the column?

     

    Please supply an example of the result you are after so it is clear what you want.

     

    Regards

     

    Phil

    • Dimitry1's avatar
      Dimitry1
      Frequent Visitor
      3 years ago

      Hi PhilipTreacy 

      I want to count the number of times "Fishing" appears before every "Discharge" appears and then get averages and so on.

       

      The result could be something like this:

      5 days Fishing -> Discharge.

      16 days Fishing-> Discharge.

      18 days Fishing-> Discharge.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Dimitry1 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

    Flag =
var _fish=MAXX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])+1),[Fishing])
return
IF(
    _fish<>[Fishing],1,0)
    Group =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]<=EARLIER('Table'[Date])),[Flag])
    Day =
var _min=
MINX(
    FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Date])
var _max=
MAXX(
    FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table'[Group])),[Date])
return
DATEDIFF(
    _min,_max,DAY)+1
    All =
[Day] &" "&"days Fishing -> Discharge"

    2. Create calculated table.

    Table 2 =
var _table=
FILTER(
    'Table',MOD([Group],2)<>0)
var _table1=
SUMMARIZE(
    _table,[Day])
return
SUMMARIZE(
    _table,[All],"Avg",AVERAGEX(_table1,[Day])
)

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly