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

[bi_quest18]

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

[Anonymous · Accepted solution]

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