Total Revenue Backlog

[Anonymous]

Ok, this seems like it should be simple but I am strugling to figure out the right dax formula.  I am trying to caluate the total revenue backlog (contrats signed but the work has not been completed) at any given time.  Do do this I need to sum all revenue associted with contracts signed prior to a date but exclude revenue where the work was completed before that same date. 

My data looks roughly like this:

Contract Signed	Campaign	Month Work Completed	Revenue
6/15/2018	Campaign E	6/1/2019	$1,000
11/6/2018	Campaign A	1/1/2019	$1,000
11/6/2018	Campaign A	2/1/2019	$1,000
11/6/2018	Campaign A	3/1/2019	$1,000
11/6/2018	Campaign A	4/1/2019	$1,000
6/25/2019	Campaign D	7/1/2019	$1,000
6/25/2019	Campaign D	9/1/2019	$1,000
6/25/2019	Campaign D	9/1/2019	$1,000
6/25/2019	Campaign D	9/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000
6/28/2019	Campaign C	7/1/2019	$1,000

In this example I would expect that if I picked the date 6/26/19, the total Backlog would be $4,000.

The relationship to my dates table is to the Month Work Completed column with an inactive link to the Contract Signed Column.  

Any help would be appretiated.  Thanks in advance.

[DataZoe]

@Anonymous I agree with @Ashish_Mathur's approach too, but here is an alterative measure too:

Backlog =
VAR maxd =
CALCULATE ( MAX ( 'Date'[Date] ) )
RETURN
MAXX (
'Date',
CALCULATE (
SUM ( 'Table'[Revenue] ),
REMOVEFILTERS ( 'Date' ),
'Table'[Contract Signed] <= maxd
&& 'Table'[Month Work Completed] >= maxd
)
) + 0

[Anonymous]

This is a HUGE help.

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.