Forum Discussion
Backward calculate backlog
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) |
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 - _culincomeBest Regards
2 Replies
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 - _culincomeBest Regards