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