Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
So far I have this:
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 )
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.
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.
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!
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])))
So far I have this:
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |