Forum Discussion
Help with Dax
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
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.
- 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.
-
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
- Visualise Amount Outstanding by Date:
Hopefully that's of some use. Please post back if required :)
Regards,
Owen
- 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 Replies
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.
- 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.
-
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
- Visualise Amount Outstanding by Date:
Hopefully that's of some use. Please post back if required :)
Regards,
Owen
- 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.
