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

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

Reply
StéphaneK
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
talespin
Solution Sage
Solution Sage

hi @StéphaneK 

 

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 @StéphaneK 

 

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

 

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.