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 get a "No", reading from the latest date to the oldest.

For example, in the case for Product "BBB" I begin from the latest date, which is 06/2022, and check ifthis date has a "Yes". In this case it has, so I count 1. Then, i check the next latest date, which is 05/2022. Since it also has a "Yes", I count 1 more. The same with 04/2022, so I count 1 more. Then, since the next latest date is 03/2022 has a "No", I stop counting. The cumulative count is 3, so that is the value I should reach in the column in red.

 

ProductProcess DateProcessed?CUMULATIVE CONSECUTIVE YES LAST PROCESSED MONTHS
AAA01-2022Yes0
AAA02-2022Yes0
AAA03-2022No0
AAA04-2022Yes0
AAA05-2022No0
AAA06-2022No0
BBB01-2022Yes3
BBB02-2022No3
BBB03-2022No3
BBB04-2022Yes3
BBB05-2022Yes3
BBB06-2022Yes3
CCC01-2022No1
CCC02-2022No1
CCC03-2022No1
CCC04-2022Yes1
CCC05-2022No1
CCC06-2022Yes1

 

Thanks!

 

Nicolás.

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

3 Replies

  • 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 )
  • besomebody20's avatar
    besomebody20
    Resolver I
    3 years ago

    Hi Nick1810 ,

    Please try this DAX Calculated Column to return the cumulative total you are looking for:

     

    Cumulated Count Processed by Product = 
    VAR _product = 'Sample Data'[Product]
    VAR _productMaxYesProcessDate = 
    CALCULATE(
        MAX('Sample Data'[Process Date]),
        FILTER(
            ALL('Sample Data'), 
            'Sample Data'[Product] = _product && 'Sample Data'[Processed?] = "Yes"
        )
    )

    VAR _productMaxNoProcessDate = 
    CALCULATE(
        MAX('Sample Data'[Process Date]),
        FILTER(
            ALL('Sample Data'), 
            'Sample Data'[Product] = _product && 'Sample Data'[Processed?] = "No"
        )
    )

    VAR _count = 
    COUNTROWS(
        CALCULATETABLE(
            'Sample Data',
            FILTER(
                ALL('Sample Data'),
                'Sample Data'[Product] = _product && 
                'Sample Data'[Process Date] > _productMaxNoProcessDate &&
                'Sample Data'[Process Date] <= _productMaxYesProcessDate
            )
        )
    )

    RETURN
    COALESCE(_count, 0)