Running total in measure with reset based upon a condition of same measure without using to a tbl

[cjmpetroleum]

I have spent hours on this forum and the internet attempting to find a solution to no avail. I beg of your assistance.

I have a measure called "NetRev" which I am attempting to have some conditional running totals on. I wish to have a rolling sum that is reset every time a new negative trend occurs.

I was able to create a running total measure called "BaseDDA2" which has a formula:

BaseDDA2 = calculate(sumx(Tax,if([NetRev]>=0,0,[NetRev])),MDate[Report].[Year]<=year(SELECTEDVALUE(MDate[Report])))

aka BaseDDA2 = calculate(sumx("only add negatives"), "filter all dates on or before current period")

the output works as expected, but the trouble I am having is attempting to reset the running total to zero when the next negative period begins (1991 in the example).

What's not worked?

• Adding NetRev to the date table in a dax measure using "addcolumn" with associated DAX formulas
• Any attempt to add NetRev as a filter condition to calculate (PLACEHOLDER error)
• Previous period references (i.e. year-1) work, but only for a single row where NetRev swings from poz to neg

If I could use a table with the earlier function, this would be a breeze, but it needs to stay dynamic due to end user constraints (sans data refresh)

If I generate a date when NetRev goes positive, and I try to use it as a date filter condition, is it still a placeholder error? I am guessing this is going wrong because the DDA is doing exactly as expected: pulling a sum of all prior NetRev<0. I need to somehow trigger a reset.

Any help would be greatly appriciated!

[cjmpetroleum]

Thank you for your efforts @some_bih ! I too have been unable to find a direct solution, but a very effective workaroud came to me today during a walk. It invovles:

1. Use Power Automate to create a single button click to export the desired data to excel (or CSV)
2. Create another table in the same or a different pbix that references the exported file as the input
3. Now all the is required is to dump the data and refresh the inputs and voila: all the data is in a table with columns where I can use the earlier function to handle the reset.

As I mentioned, this is not a true solution, but a elegant work-around. I will be running many scenarios, but it's not an unmanageable number, nor a unwieldly sized dataset. If done properly, I can get the behavior I am looking for with only two clicks.

I appriciate you @some_bih ! Thanks again!

[some_bih]

Hi @cjmpetroleum I really tried for hours to get you desirable output, but I could find correct approach.

I tried for various combination on row level to create measure or to use IF formulas, but as shown on picture below I did not get correct output.  I even tried to implement previous row data but as there are so many combination like from positive number to negative or two days are negative I could not find pattern to create IF statement.
Wish you more luck with this.

[some_bih]

Hi @cjmpetroleum if you want negative  part   [M_Amount] replace with 0 in

IF(_Negative_Amount<0,

        [M_Amount],

Or provide Expected columns data.

PS I am writing this via mobile

[cjmpetroleum]

Thank you @some_bih 

I have created a sample pbix with some additional explaination within it. I have attempted to load it with the formulas you suggested, but I cannot run a SUM on the target measure.

Many thanks for your help!

https://drive.google.com/file/d/1OPBzJwMVHqhPIcTUyeou7-4GBj8aoytW/view?usp=sharing

[some_bih]

Hi @cjmpetroleum I create simple excel data from your picture and in Excel create "test amount".

In PowerBI I created Date Table and relationship with Year column (I import Excel as Date table in format Date dd.mm.yyyy) and following measures. Assumption for Reset Measure is that when negative amount is the year,  I hope this is ok for you.

M_Amount = sum(Sheet2[Amount])

VAR _MaxDate=MAX('Date'[Date])

VAR _Result=

    CALCULATE(

        [M_Amount],

        'Date'[Date]<=_MaxDate,

        ALL('Date')

    )

RETURN _Result

M_Amount_RunTotal_Reset =

VAR _MaxDate=MAX('Date'[Date])

VAR _Negative_Amount = [M_Amount]

VAR _Result=

    IF(_Negative_Amount<0,

        [M_Amount],

            CALCULATE(

                [M_Amount],

                'Date'[Date]<=_MaxDate,

                ALL('Date')

            )

    )

RETURN _Result

Excel "test data"

PowerBI

[cjmpetroleum]

Hello @some_bih 

I appriciate this information and the effort, but my "sum" column is not values, it is a measure. Thus M_Amount = sum(Sheet2[Amount]) will not work as Sheet2[Amount] is a measure (in my case) and not a column. The sum function can only work on columns. If I could somehow convert my measure to a column (without addcolumn, as this does not appear to work properly), then I believe your solution would work.

Thank you for your efforts, but sadly for me, this solution did not work as-is.