Forum Discussion

Nick1810's avatar
Nick1810
Frequent Visitor
3 years ago
Solved

Cumulative Count Per Month Unitl Some Condition

Hello!   I am trying to generate a COLUMN with the data in red, based on the table below. What I am trying to get is the Acumulative Count of the consecutives "Yes" processes per Product until you...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Nick1810 

    please try

    Acumulative Count =
VAR CurrentProductTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR NoTable =
    FILTER ( CurrentProductTable, 'Table'[Processed?] = "No" )
VAR LastNoRecord =
    TOPN ( 1, NoTable, 'Table'[Process Date] )
VAR LastNoDate =
    MAXX ( LastNoRecord, 'Table'[Process Date] )
VAR YesTable =
    FILTER ( CurrentProductTable, 'Table'[Processed?] = "Yes" )
VAR YesAfterNoTable =
    FILTER ( YesTable, 'Table'[Process Date] > LastNoDate )
RETURN
    COUNTROWS ( YesAfterNoTable )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Nick1810 

please try

Acumulative Count =
VAR CurrentProductTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR NoTable =
    FILTER ( CurrentProductTable, 'Table'[Processed?] = "No" )
VAR LastNoRecord =
    TOPN ( 1, NoTable, 'Table'[Process Date] )
VAR LastNoDate =
    MAXX ( LastNoRecord, 'Table'[Process Date] )
VAR YesTable =
    FILTER ( CurrentProductTable, 'Table'[Processed?] = "Yes" )
VAR YesAfterNoTable =
    FILTER ( YesTable, 'Table'[Process Date] > LastNoDate )
RETURN
    COUNTROWS ( YesAfterNoTable )
Nick1810's avatar
Nick1810
Frequent Visitor
3 years ago

Genious!! Thankss