Cumulative months if the result is negative, until the positive month

[Anonymous]

Hello,

I have two tables: one with forecast and one with stock, related by a date table.

I try to create a measure, to be used in a matrix graph (year and month on culumns and article on rows), that will calculate every month Stock-Forecast and cumulate the result in the following months only if the result from the prev. month is negative. If is positive, the next month will start again to cumulate those moths that are negative.

Here is what I have so far:

Measure =

VAR CurrentValue = CALCULATE([minus BBD 7-12],FILTER(ALL('Calendar'[Date]),'Calendar'[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && 'Calendar'[Date]<=MAX('Calendar'[Date])))

VAR PrevValue =

    CALCULATE (

        FILTER(ALL('Calendar'[Date]),'Calendar'[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && 'Calendar'[Date]<=MAX('Calendar'[Date])),

        OFFSET (

            -1,

            VALUES ( 'Calendar'[Date] ),

            ORDERBY ( 'Calendar'[Date], ASC )

        )

    )

RETURN

    CurrentValue + MAX ( 0, PrevValue )

But this measure is not resetting when a positive result in the prev. month

minus BBD 7-12 = Sum('StockKLG'[QTY])-SUM('dw_si Forecast'[QTY])

This is the expected outcome:

[Anonymous]

Hi @Anonymous ,

Dax is not available. And Dax can't do iterations. You can achieve it in Power Query.

I have found a similar post, please refer to if to see if it helps you.

Power Query - Cumulated row, if a certain value is matched 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.