Help with Dax

[ylemire]

Hi all,

I am working on a situation where i do need help. Below is the scenario,

Suppose that i'm invoicing 18 customer the firt day of the month, the due date is end of the month, I want to be able to vizualise the total of invoice made the first day of the month and as the payment are made over the time , the cumlative total decreasing.

Here a sample of my data and the graphic that I made in Excel as an example of what i'm trying to do

Sell-to Customer Name	No_Invoice	Posting Date	Due Date	Closed at Date
Customer1	FVR104007	2019-02-01	2019-02-28	2019-02-22
Customer2	FVR104008	2019-02-01	2019-02-28	2019-02-22
Customer3	FVR103995	2019-02-01	2019-02-28	2019-02-25
Customer4	FVR104009	2019-02-01	2019-02-28	2019-02-25
Customer5	FVR104011	2019-02-01	2019-02-28	2019-02-25
Customer6	FVR103999	2019-02-01	2019-02-28	2019-02-27
Customer7	FVR103997	2019-02-01	2019-02-28	2019-02-28
Customer8	FVR104004	2019-02-01	2019-02-28	2019-02-28
Customer9	FVR104010	2019-02-01	2019-02-28	2019-02-28
Customer10	FVR103996	2019-02-01	2019-02-28	2019-03-05
Customer11	FVR104006	2019-02-01	2019-02-28	2019-03-05
Customer12	FVR104000	2019-02-01	2019-02-28	2019-03-11
Customer13	FVR104002	2019-02-01	2019-02-28	2019-03-12
Customer14	FVR104001	2019-02-01	2019-02-28	2019-03-15
Customer15	FVR103998	2019-02-01	2019-02-28	2019-03-20
Customer16	FVR104012	2019-02-01	2019-02-28	2019-04-11
Customer17	FVR104005	2019-02-01	2019-02-28	2019-04-18
Customer18	FVR104003	2019-02-01	2019-02-28	2019-05-01

Can you please help me how to achieve this logic by writing DAX ?

Thanks

[Ashish_Mathur]

Hi,

You may download the PBI file from here.

Hope this helps.

[OwenAuger · Accepted solution]

@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