Calculating overdues over time with DAX during the last 12 months

[Anonymous]

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

[talespin · Accepted solution]

hi @Anonymous 

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

[Anonymous]

Many thanks !

[lbendlin]

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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...