cancel
Showing results for
Did you mean:
bi_quest18 Frequent Visitor

DAX to find remainder row starting from current row (dynamically)

Hi All, Cannot figure out how to write a measure for this?

I have data as follows

Date InvAmt SalesAmt

20180821 1000 1500

20180814 1000 400 20180807 1000 600 20180801 1000 1100

20180724 1000 1500

I need a measure called NegativeWeek that gives me the date where the running remainder of InvAmt - SalesAmt starting from the current row went <= 0

e.g. For 20180814 InvAmt-SalesAmt = 600, we start here with 600 as Remainder, we calculate 20180807, we do 600 remainder - 600 (SalesAmt on this row) = 0. So for 201808014, NegativeWeek = 20180807

For 20180807, InvAmt - SalesAmt = 400, we start here with 400 as Remainder, we calculate 20180801, we do 400 remainder - 1100 (SalesAmt on this row) = -700, So for 20180807, NegativeWeek = 20180801 Hope i made this clear, the dates are descending (we start from a date and calculate remainder going back).

I cannot do this as calculated columns since the remainderAmt is dynamic based on which week we start from.

Any help? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: DAX to find remainder row starting from current row (dynamically)

HI @bi_quest18,

You can take a look at following calculate column formulas to find out rolling remainder:

Non Zero =
IF ( [InvAmt] - [SalesAmt] <= 0, BLANK (), [Index] )

Rolling Remainder =
VAR rangeStart =
IF (
[Non Zero] <> BLANK (),
CALCULATE (
MAX ( 'Original'[Index] ) + 1,
FILTER (
ALL ( 'Original' ),
[Index] <= EARLIER ( [Index] )
&& [Non Zero] = BLANK ()
)
)
)
VAR rangeEnd =
IF (
[Non Zero] <> BLANK (),
CALCULATE (
MIN ( 'Original'[Index] ) - 1,
FILTER (
ALL ( 'Original' ),
[Index] >= EARLIER ( [Index] )
&& [Non Zero] = BLANK ()
)
)
)
VAR filtered =
FILTER ( ALL ( 'Original' ), [Index] >= rangeStart && [Index] <= rangeEnd )
RETURN
IF (
[Non Zero] <> BLANK (),
[InvAmt]
- SUMX ( FILTER ( filtered, [Index] <= EARLIER ( [Index] ) ), [SalesAmt] )
) Test sample: Notice:

1. Your date column need to write complex convert formula to find out nearest next/previous records, so I add index column in query editor for looping through table.

2. 'Non Zero' column is used to ignore records who not suitable for your conditions.

3. I attached sample pbix file below for reference.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | | Community Support Team

Re: DAX to find remainder row starting from current row (dynamically)

HI @bi_quest18,

You can take a look at following calculate column formulas to find out rolling remainder:

Non Zero =
IF ( [InvAmt] - [SalesAmt] <= 0, BLANK (), [Index] )

Rolling Remainder =
VAR rangeStart =
IF (
[Non Zero] <> BLANK (),
CALCULATE (
MAX ( 'Original'[Index] ) + 1,
FILTER (
ALL ( 'Original' ),
[Index] <= EARLIER ( [Index] )
&& [Non Zero] = BLANK ()
)
)
)
VAR rangeEnd =
IF (
[Non Zero] <> BLANK (),
CALCULATE (
MIN ( 'Original'[Index] ) - 1,
FILTER (
ALL ( 'Original' ),
[Index] >= EARLIER ( [Index] )
&& [Non Zero] = BLANK ()
)
)
)
VAR filtered =
FILTER ( ALL ( 'Original' ), [Index] >= rangeStart && [Index] <= rangeEnd )
RETURN
IF (
[Non Zero] <> BLANK (),
[InvAmt]
- SUMX ( FILTER ( filtered, [Index] <= EARLIER ( [Index] ) ), [SalesAmt] )
) Test sample: Notice:

1. Your date column need to write complex convert formula to find out nearest next/previous records, so I add index column in query editor for looping through table.

2. 'Non Zero' column is used to ignore records who not suitable for your conditions.

3. I attached sample pbix file below for reference.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

Announcements Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Top Kudoed Authors
Users Online
Currently online: 260 members 2,753 guests
Recent signins:
• Oddjobe • Nickgastaldi • richank • k3rz0rg • jasonmaskell • rrafferty37 • tomas789 • aagarwal2 • oabd0250 • SERGIO_MORETO • lisakuehl3 • zdthompson5 • jailenwong • im_newbie_123 