Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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) |
Solved! Go to Solution.
Hi @mike999 ,
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
Hi @mike999 ,
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
Thank you Rena. That appears to be working - Thank you so much 👍