cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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: | |

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 288 members 3,179 guests
Please welcome our newest community members: