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

Anonymous — Mon, 20 Mar 2023 08:05:56 GMT

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:

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

Anonymous — Wed, 22 Mar 2023 02:10:04 GMT

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.

topic Re: Cumulative months if the result is negative, until the positive month in DAX Commands and Tips

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

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