cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Calculating overdues over time with DAX during the last 12 months

Hi,

I'm trying to calculate overdue receivables at the last 12 months, i.e how many receivables were overdue in January, February ...

I've got a transactions table ("Data") with the following columns : [Amount], [Due Date], [Invoice Date], [Paid Date].

I have also and Date table called "Calendar".

I would like to calculate overdues as invoices which have  [Paid Date] = '2099-01-01' (i.e. have not been paid yet / are still oustanding), and [Due Date] < "the date of the period last 12 month".

I'm still having trouble to find out the Dax code.

Many thanks,

Stéphane

1 ACCEPTED SOLUTION
Solution Sage

This measure will give you running total of Dues each month. For show only last 12 months I have applied date filter on visual(Screenshot).

This will give you all dues since beginning/Min date in your Calendar table, if you want to put a start date, just add Calendar[Date] >=  in Calculate.

Dues  =
VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])
VAR _Mth = SELECTEDVALUE('CALENDAR'[MonthNo])
VAR _Dt = EOMONTH(DATE(_Year, _Mth, 1),0)

RETURN CALCULATE( SUM(Txn[Amount]), Txn[PaidDate] = CONVERT("2099-01-01",DATETIME), 'CALENDAR'[Date] <= _Dt)

Sample Data I have used

TxnIDDueDateInvoiceDatePaidDateAmount

 1 15 January 2023 01 January 2099 200 3 16 May 2023 01 January 2099 900 4 23 May 2023 01 January 2099 500 6 10 June 2023 01 January 2099 600 7 22 June 2023 01 January 2099 500 17 19 July 2023 01 January 2099 500 14 22 July 2023 01 January 2099 600 10 09 August 2023 01 January 2099 100 12 09 September 2023 01 January 2099 500 18 15 September 2023 01 January 2099 200 8 02 October 2023 01 January 2099 200 23 06 November 2023 01 January 2099 600 20 16 November 2023 01 January 2099 900 21 19 November 2023 01 January 2099 500 15 09 December 2023 01 January 2099 500

3 REPLIES 3
Solution Sage

This measure will give you running total of Dues each month. For show only last 12 months I have applied date filter on visual(Screenshot).

This will give you all dues since beginning/Min date in your Calendar table, if you want to put a start date, just add Calendar[Date] >=  in Calculate.

Dues  =
VAR _Year = SELECTEDVALUE('CALENDAR'[YEAR])
VAR _Mth = SELECTEDVALUE('CALENDAR'[MonthNo])
VAR _Dt = EOMONTH(DATE(_Year, _Mth, 1),0)

RETURN CALCULATE( SUM(Txn[Amount]), Txn[PaidDate] = CONVERT("2099-01-01",DATETIME), 'CALENDAR'[Date] <= _Dt)

Sample Data I have used

TxnIDDueDateInvoiceDatePaidDateAmount

 1 15 January 2023 01 January 2099 200 3 16 May 2023 01 January 2099 900 4 23 May 2023 01 January 2099 500 6 10 June 2023 01 January 2099 600 7 22 June 2023 01 January 2099 500 17 19 July 2023 01 January 2099 500 14 22 July 2023 01 January 2099 600 10 09 August 2023 01 January 2099 100 12 09 September 2023 01 January 2099 500 18 15 September 2023 01 January 2099 200 8 02 October 2023 01 January 2099 200 23 06 November 2023 01 January 2099 600 20 16 November 2023 01 January 2099 900 21 19 November 2023 01 January 2099 500 15 09 December 2023 01 January 2099 500

Frequent Visitor
Many thanks !
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.