topic Re: Calculating overdues over time with DAX during the last 12 months in DAX Commands and Tips

Calculating overdues over time with DAX during the last 12 months

Anonymous — Fri, 16 Feb 2024 21:39:15 GMT

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

Re: Calculating overdues over time with DAX during the last 12 months

lbendlin — Sat, 17 Feb 2024 02:59:27 GMT

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-Forum/ba-p/963216

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/1447523

Re: Calculating overdues over time with DAX during the last 12 months

talespin — Sat, 17 Feb 2024 04:02:27 GMT

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

Re: Calculating overdues over time with DAX during the last 12 months

Anonymous — Mon, 26 Feb 2024 08:07:04 GMT

Many thanks !