topic Re: Cumulative Count Per Month Unitl Some Condition in DAX Commands and Tips

Cumulative Count Per Month Unitl Some Condition

Nick1810 — Tue, 01 Nov 2022 17:07:59 GMT

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.

Re: Cumulative Count Per Month Unitl Some Condition

tamerj1 — Tue, 01 Nov 2022 17:54:17 GMT

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 )

Re: Cumulative Count Per Month Unitl Some Condition

besomebody20 — Tue, 01 Nov 2022 17:56:03 GMT

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)

Re: Cumulative Count Per Month Unitl Some Condition

Nick1810 — Tue, 01 Nov 2022 19:26:28 GMT

Genious!! Thankss