# DAX Challenge - Recursive

Hi guys,
We Know something DAX is hard with recursive calculations, but this goes another level.

The rule is in the fact table (fAmount), i ahve to increment when it is negative, when the next month goes positive (Cumulative) than it should reset the cumulate and next month start with 0.

If i have
month 1 = - 10
month 2 = -50 (cumulative: -60)
month 3 = +100 (cumulative: +40 , so because i have positive bank roll, next month start with 0, not with the +40
month 4 = -20 (Cumulative: -20)

example:
in excel is easy to do
Iâ€™ll attach an example as wellâ€¦

Really didnâ€™t figure out a way to do itValor Pago (1).pbix (97.9 KB) Saldo Imposto.xlsx (10.9 KB)

Really interesting problem. I developed a solution a few months ago for a similar problem that I think will be very useful here to create an intermediate 0/1 measure based on sign change that we can then use trigger reset of the cumulative total measure.

Iâ€™ll play around with this today, and hopefully have something back to you soon.

• Brian
1 Like

While this one is difficult to solve in DAX, itâ€™s pretty straightforward if you do it in Power Query. Iâ€™m 95% of the way to a solution â€“ just need to do some final M code debugging after I get some sleep. Will have a working solution for you on Sunday.

• Brian

OK, please check this to be sure, but I think I have a working solution here. As I mentioned above, the solution is orders of magnitude easier IMO if done in Power Query than in DAX. Most of the time when youâ€™re traversing up and down columns to locate specific values, PQ will be a lot easier than DAX. So hereâ€™s the outline of what I did:

The first thing was to conceptually sketch out that we would need a column like the one highlighted in red below that was an indicator of the breakpoints for resetting the cumulative total, based on your decision rules The specific values in this column donâ€™t matter - just the relative position of when they change.

The next steps are all in PQ: (note: this seems quite complicated, but Iâ€™m confident that when you work through the logic of what Iâ€™ve done via the applied steps in PQ it will make good sense to you. If not, just give a shout -Iâ€™'m happy to answer questions.)

1. Add an Index column starting at 1 - this is the key to traversing up and down the columns
2. Calculate the cumulative total in a Custom column, using the following M code:

`List.Sum(List.FirstN(#"Changed Type"[Amount],[Index]))`

1. Duplicate the index column, add +1 to each value and rename it â€śIndex Add 1â€ť.

2. Merge the fAmount table with itself, with Index and Index Add 1 as the keys:

1. Expand the tables created and select the new Cumul Amt and change the name to Prev Cumul Amount.

2. Create another custom column called Cumul Reset (this is the field that will control the breakpoint resets for the cumulative totals), using the following M code to detect a change in sign:

if [Index] = 1 then 1 else
if (Number.Sign( [Cumul Amt] ) = Number.Sign( [Prev Cumul Amt] )) then null else [Index]

3. Sort the table by index, and then fill down on the newly created column to replace the nulls with the same breakpoint value, all the way down to the next breakpoint value.

4. Do the same merge as in 4) above again. This is because your cumulative total resets the record after a sign change is detected.

5. Do the same expansion and fill down as above in 5), but this time on the newly created Cumul Reset column. Youâ€™ll also need to end this with a Fill Up command. Rename column to "Shifted Cumul Reset))

6. Change all the relevant types to numeric, close and apply, and you should have the table above, missing the final column.

7. For that column, use this measure:

` Cumul Amt Reset =`

``````    CALCULATE(
[Total Amount],
FILTER(
ALL( Dates ),
Dates[Date] <= MAX( Dates[Date] )
),
ALLEXCEPT(
fAmount,
Dates[Date],
fAmount[Shifted Cumul Reset]
)
)
``````

Add that measure to your table, and hopefully youâ€™re good to go:

I hope this is helpful. Full solution file attached.

Very cool problem - I learned a lot working on it.

4 Likes

Brian,
I have no words to describe how much i appreciate your effort.
Iâ€™ll check out the solution and how i could apply in my model

Really cool exercise right? iâ€™m also learning a lot in this one
thanks mate
Regards

My pleasure . You did a great job laying out the problem and providing all the necessary info. And Iâ€™m working improving my PQ/M skills, so win-winâ€¦

• Brian
1 Like

Amazing work on this Brian