Forum Discussion

damj's avatar
damj
Frequent Visitor
6 years ago
Solved

consecutive day

Hi.

Trying to create a column that counts consecutive days based on "Eventkode" sorted in "Litra" like the illustration shown below.

Hope someone can help.

Thanks in advance.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi damj 

    Create calculated columns

    clc1 = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[litra]),[date]<=EARLIER('Table'[date])))

earlier day = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[litra]),'Table'[date]=EARLIER('Table'[date])-1))

clc 2 = IF([earlier day]=BLANK(),1,[clc1])
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi damj 

     

    Try this column.

    Column = 
VAR _tbl = CALCULATETABLE( VALUES( 'Table'[Date] ), ALLEXCEPT(  'Table', 'Table'[Litra] ) )
RETURN 
RANKX( _tbl, CALCULATE( MAX( 'Table'[Date] ) ),,DESC )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • damj's avatar
      damj
      Frequent Visitor
      6 years ago

      Hi Mariusz.

      Tried to implement the code, but it didn't have the desired effect.

      I need "Eventcode" to count up for each day it triggers when consecutive, and start from 1 when not. Sorted in the different "Litra" = (Machinenr).

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi damj 

    Create calculated columns

    clc1 = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[litra]),[date]<=EARLIER('Table'[date])))

earlier day = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[litra]),'Table'[date]=EARLIER('Table'[date])-1))

clc 2 = IF([earlier day]=BLANK(),1,[clc1])
     
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.