Help with Dax

ylemire 

There are a few ways to do this.

You could handle this as an "events in progress" measure (numerous examples online), or simply calculate the cumulative posted less cumulative closed.

I've opted for the 2nd approach in the attached file.

1. Create Invoices table related to Date table as shown. Active relationship with Posting Date, inactive relationship with Closed at Date. Note that I gave a value of 1 to each of the Invoices. I'm not sure if you will have specific values per invoice or simply count the invoices, in which case you could change the SUMs below to COUNTROWS or similar.

    

2. Create measures as follows:
   
   

   Amount Posted = 
   SUM ( Invoices[Amount] )
   
   Amount Closed = 
   CALCULATE ( 
       [Amount Posted],
       USERELATIONSHIP ( Invoices[Closed at Date], 'Date'[Date] )
   )
   
   Amount Posted Cumulative = 
   VAR MaxDate =
       MAX ( 'Date'[Date] )
   RETURN
   CALCULATE ( 
       [Amount Posted],
       'Date'[Date] <= MaxDate
   )
   
   Amount Closed Cumulative = 
   VAR MaxDate =
       MAX ( 'Date'[Date] )
   RETURN
   CALCULATE ( 
       [Amount Closed],
       'Date'[Date] <= MaxDate
   )
   
   Amount Outstanding = 
   VAR GlobalMaxClosedDate =
       CALCULATE ( MAX ( Invoices[Closed at Date] ), ALL () )
   RETURN
       IF (
           MIN ( 'Date'[Date] ) <= GlobalMaxClosedDate,
           [Amount Posted Cumulative] - [Amount Closed Cumulative]
       )

    

    The last measure is effectively blanked out for dates greater than the maximum Closed at Date

3. Visualise Amount Outstanding by Date:

    

Hopefully that's of some use. Please post back if required :)

Regards,

Owen