Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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
talespin
Solution Sage
Solution Sage

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)
 

talespin_0-1708142014731.png

 

Sample Data I have used

TxnIDDueDateInvoiceDatePaidDateAmount

115 January 2023 01 January 2099200
316 May 2023 01 January 2099900
423 May 2023 01 January 2099500
610 June 2023 01 January 2099600
722 June 2023 01 January 2099500
1719 July 2023 01 January 2099500
1422 July 2023 01 January 2099600
1009 August 2023 01 January 2099100
1209 September 2023 01 January 2099500
1815 September 2023 01 January 2099200
802 October 2023 01 January 2099200
2306 November 2023 01 January 2099600
2016 November 2023 01 January 2099900
2119 November 2023 01 January 2099500
1509 December 2023 01 January 2099500

 

talespin_1-1708142446973.png

 

View solution in original post

3 REPLIES 3
talespin
Solution Sage
Solution Sage

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)
 

talespin_0-1708142014731.png

 

Sample Data I have used

TxnIDDueDateInvoiceDatePaidDateAmount

115 January 2023 01 January 2099200
316 May 2023 01 January 2099900
423 May 2023 01 January 2099500
610 June 2023 01 January 2099600
722 June 2023 01 January 2099500
1719 July 2023 01 January 2099500
1422 July 2023 01 January 2099600
1009 August 2023 01 January 2099100
1209 September 2023 01 January 2099500
1815 September 2023 01 January 2099200
802 October 2023 01 January 2099200
2306 November 2023 01 January 2099600
2016 November 2023 01 January 2099900
2119 November 2023 01 January 2099500
1509 December 2023 01 January 2099500

 

talespin_1-1708142446973.png

 

Anonymous
Not applicable

Many thanks !
lbendlin
Super User
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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.