topic Re: if condition with months and positive/negative values in DAX Commands and Tips

if condition with months and positive/negative values

Anonymous — Fri, 10 Mar 2023 10:32:07 GMT

Hi everyone,

I have the following results so far:

One column with Stock and one with Forecast that are comming from SQL, and a third column (BBD Risk 7-12), that is a calculated measure:

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

minus BBD 7-12 = [QTY BBD 7-12] (Stock) - SUM('dw_si Forecast'[Value]) (Forecast)

This is the result from BBD Risk 7-12 measure and the expected result:

What I want is to cumulate the result from every month until it will find a positive result from Stock-Forecast, then it will start again to cumulate the result from the next month of postive resul, until the next one.

Re: if condition with months and positive/negative values

johnt75 — Fri, 10 Mar 2023 12:18:54 GMT

Try

Resetting accumulator = VAR CurrentValue = [minus BBD 7-12] VAR PrevValue = CALCULATE ( [minus BBD 7-12], OFFSET ( -1, VALUES ( 'Calendar'[Year month] ), ORDERBY ( 'Calendar'[Year month], ASC ) ) ) RETURN CurrentValue + MAX ( 0, PrevValue )

Re: if condition with months and positive/negative values

Anonymous — Fri, 10 Mar 2023 12:03:18 GMT

johnt75 Thnak you!

Error in the Measure. What is REL?

Re: if condition with months and positive/negative values

johnt75 — Fri, 10 Mar 2023 12:05:33 GMT

My mistake. I started writing it as one of the other window functions and then realised that OFFSET was better, but it doesn't need REL. Just delete that line.

Re: if condition with months and positive/negative values

Anonymous — Fri, 10 Mar 2023 12:15:32 GMT

I don't really know how to write it. Can you write it for me and explain how it works?

Re: if condition with months and positive/negative values

johnt75 — Fri, 10 Mar 2023 12:21:11 GMT

I've amended my original post to remove the REL, you should be able to use that now.

It works by creating a window over the year month column from the date table and using this to find the value for the previous month. If that value is -ve then it will add it to the current value, otherwise it will just return the current value as is.

Re: if condition with months and positive/negative values

Anonymous — Fri, 10 Mar 2023 12:37:23 GMT

Not quite what I've wanted:

In the picture for July the rusult needs to be -172-105=-277 All the negative results from a month need to cumulate in the next month, until the return of a positive result.

Then in August 1759-109+(-277 "Cumulative result until that month)=1373

The in September, because in August we had a positive value, it will start again to add cumulative result, until the next positive result.

Maybe I didn't explain very well. If it was not understood, I can look for an explanation in another form.

Thank you!

Re: if condition with months and positive/negative values

Anonymous — Fri, 10 Mar 2023 12:40:50 GMT

Also, the calculateion needs to start from the current month, like it was in my first formula:

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

Re: if condition with months and positive/negative values

Anonymous — Mon, 13 Mar 2023 07:35:47 GMT

So far I have this:

BBD Risk =

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 )

The problem is that the result in a month is not reseting when in prev. month there is a positive result.

With Green is the desired result:

Re: if condition with months and positive/negative values

Anonymous — Tue, 14 Mar 2023 12:36:25 GMT

So far I have this:

BBD Risk =

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 )

The problem is that the result in a month is not reseting when in prev. month there is a positive result.

With Green is the desired result: