Cumulative Count Per Month Unitl Some Condition

[Nick1810]

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.

Product	Process Date	Processed?	CUMULATIVE CONSECUTIVE YES LAST PROCESSED MONTHS
AAA	01-2022	Yes	0
AAA	02-2022	Yes	0
AAA	03-2022	No	0
AAA	04-2022	Yes	0
AAA	05-2022	No	0
AAA	06-2022	No	0
BBB	01-2022	Yes	3
BBB	02-2022	No	3
BBB	03-2022	No	3
BBB	04-2022	Yes	3
BBB	05-2022	Yes	3
BBB	06-2022	Yes	3
CCC	01-2022	No	1
CCC	02-2022	No	1
CCC	03-2022	No	1
CCC	04-2022	Yes	1
CCC	05-2022	No	1
CCC	06-2022	Yes	1

Thanks!

Nicolás.

[besomebody20]

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)

[tamerj1 · Accepted solution]

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]

Genious!! Thankss