Forum Discussion

kbrvy's avatar
kbrvy
Regular Visitor
1 year ago
Solved

Rolling Sum Until Certain Date

I am trying to sum past due and not past due order balances. I have order line items with a value, due date, and ship date. I want to sum the value up until the due date to use in a visual as "Not Pa...
DAX
  • techies's avatar
    techies
    1 year ago

    Hi kbrvy please try this

    Past Due Balance =
    VAR CurrentDate = SELECTEDVALUE('Calendar'[Date])
    RETURN
    CALCULATE(
        SUM(Orders[Value]),
        FILTER(
            ALL(Orders),  
            Orders[Due Date] < CurrentDate &&
            Orders[Ship Date] > CurrentDate
        ),
        USERELATIONSHIP(Orders[Ship Date], 'Calendar'[Date])
    )
     
    Not Past Due Balance =
    VAR CurrentDate = SELECTEDVALUE('Calendar'[Date])
    RETURN
    CALCULATE(
        SUM(Orders[Value]),
        FILTER(
            Orders,
            Orders[Due Date] >= CurrentDate &&
            Orders[Ship Date] > CurrentDate
        ),
        USERELATIONSHIP(Orders[Ship Date], 'Calendar'[Date])
    )
techies's avatar
techies
Icon for Super User rankSuper User
1 year ago

Hi kbrvy please try this

Past Due Balance =
VAR CurrentDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Orders[Value]),
    FILTER(
        ALL(Orders),  
        Orders[Due Date] < CurrentDate &&
        Orders[Ship Date] > CurrentDate
    ),
    USERELATIONSHIP(Orders[Ship Date], 'Calendar'[Date])
)
 
Not Past Due Balance =
VAR CurrentDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
    SUM(Orders[Value]),
    FILTER(
        Orders,
        Orders[Due Date] >= CurrentDate &&
        Orders[Ship Date] > CurrentDate
    ),
    USERELATIONSHIP(Orders[Ship Date], 'Calendar'[Date])
)