DAX Challenge - Recursive

[andrejaar]

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)

[BrianJ]

@andrejaar,

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

Count Consecutive Months that a measure was positive or negative

@nickjordan32, This is a really interesting problem that I’ve been thinking about since you posted it initially, and think I finally cracked it. Had to throw a lot of DAX at it - here are the highlights: Used a series of table functions to create a physical table (pTable) summarizing the relevant fields and measures, where Total Over Under was not blank (but could be zero): pTable = VAR vTable= FILTER( ADDCOLUMNS( SUMMARIZE( Dates, Dates[MonthInCalenda…

[BrianJ]

@andrejaar,

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

[BrianJ]

@andrejaar,

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

3. Duplicate the index column, add +1 to each value and rename it “Index Add 1”.

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

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

6. 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]

7. 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.

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

9. 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))

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

11. 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.

• Brian
  e DNA Forum – DAX Challenge Recursive Solution,pbix.pbix (186.1 KB)

[andrejaar]

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

[BrianJ]

@andrejaar,

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

[sam.mckay]

Amazing work on this Brian