cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bi_quest18 Frequent Visitor
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
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] )
    )

9.PNG

 

Test sample:

10.PNG

 

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: | |
1 REPLY 1
Community Support Team
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] )
    )

9.PNG

 

Test sample:

10.PNG

 

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 260 members 2,753 guests
Please welcome our newest community members: