Backward calculate backlog

[Anonymous]

I have the table below in Power BI ("table1") and I need to calculate the values in red working backwards.

calculation for the "known" backlog value for tiday. xOpenCountToday = CALCULATE(count(_Active[ID]),ALL(table1[Date]),'table1'[Date]=TODAY())

I've tried all the various examples on this community board, but just can't get it to work. What is the DAX statement that I should be using (I'm new to dax)? If I was using SQL that I would use lead and lag window functions.

Thank you in advance for your time and any assistance you can provide.

Date	Name	Incoming	Outgoing	Backlog
10-Apr	UserA	10	12	33
11-Apr	UserA	4	4	33
12-Apr	UserA	6	2	37
13-Apr	UserA	8	4	41
14-Apr	UserA	3	2	42
15-Apr	UserA	1	4	39		value=backlog from next day (39) +Outgoing for next day (3) - Incoming for next day (3) = 39
16-Apr	UserA	3	3	39		value=backlog from next day (40) +Outgoing for next day (0) - Incoming for next day (1) = 39
17-Apr	UserA	1	0	40		Value (40) in this is known (see above)
10-Apr	UserB	22	12	57
11-Apr	UserB	5	7	55
12-Apr	UserB	2	6	51
13-Apr	UserB	6	5	52
14-Apr	UserB	8	3	57
15-Apr	UserB	2	2	57		value=backlog from next day (59) +Outgoing for next day (1) - Incoming for next day (3) = 57
16-Apr	UserB	3	1	59		value=backlog from next day (60) +Outgoing for next day (0) - Incoming for next day (1) = 59
17-Apr	UserB	1	0	60		Value (60) in this is known (see above)

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as belwo to get it, please find the details in the attachment.

Backlog = 
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _selname =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _culincome =
    CALCULATE (
        SUM ( 'Table'[Incoming] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
VAR _culoutcome =
    CALCULATE (
        SUM ( 'Table'[Outgoing] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = _selname
                && 'Table'[Date] > _seldate
        )
    )
RETURN
    [xOpenCountToday] + _culoutcome - _culincome

Best Regards

[Anonymous]

Thank you Rena. That appears to be working - Thank you so much 👍