cancel
Showing results for
Did you mean:
Highlighted
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 150020180814 1000 400

20180807 1000 600

20180801 1000 110020180724 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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 288 members 3,179 guests
Recent signins: