Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

if condition with months and positive/negative values

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:
adriandumitru_0-1678443995099.png

 

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.

9 REPLIES 9
Anonymous
Not applicable

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:
adriandumitru_0-1678692926276.png

 

johnt75
Super User
Super User

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 )

 

Anonymous
Not applicable

@johnt75 Thnak you! 

Error in the Measure. What is REL?

adriandumitru_0-1678449774621.png

 

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.

Anonymous
Not applicable

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

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.

Anonymous
Not applicable

Not quite what I've wanted:

adriandumitru_0-1678451392798.png

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!

Anonymous
Not applicable

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

Anonymous
Not applicable

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 ( 0PrevValue )
 
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:
adriandumitru_0-1678797375420.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors