cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Measure for overdue invoices report

Hello,

Newbie here. I need to calculate the number of invoices which were overdue on the last day of the month for the last 12 months. The criteria for an invoice to be considered overdue is:

Parked Date < X < Clearing Date , and X > Net Due Date (where X is the last day of a given month)

Here below is a sample of my 2 tables. I have a main relationship between Calendar[Date] and Overdues_DB[ParkedDate], and secondary relationships between Calendar[Date] and Overdues_DB[ClearingDate] and Overdues_DB[NetDueDate].

Calendar

 Date Month-Year 29.07.2019 19-Jul 30.07.2019 19-Jul 31.07.2019 19-Jul 01.08.2019 19-Aug 02.08.2019 19-Aug 03.08.2019 19-Aug

Overdues_DB

 Invoice ID ParkedDate NetDueDate ClearingDate 200009-65-2019 11.07.2019 23.07.2019 08.08.2019 200010-65-2019 11.07.2019 22.07.2019 25.07.2019 200011-65-2019 12.07.2019 28.06.2019 07.08.2019 200014-65-2019 25.07.2019 07.08.2019 15.08.2019 200016-65-2019 29.07.2019 01.07.2019 29.07.2019

Here is my attempt but since the main relationship is with ParkedDate, only the first IF is working. I tried adding USERELATIONSHIP into the formula but I get error messages and I dont really know if this is even possible.

VAR Lastday = MAX('Calendar'[Date])

OverdueInvoices_Count = CALCULATE( DISTINCTCOUNT( Overdues_DB[InvoiceID]),
FILTER(Overdues_DB, Overdues_DB [ParkedDate] < [VAR Lastday] &&
[VAR Lastday] < Overdues_DB[ClearingDate] &&
[VAR Lastday] < Overdues_DB[NetDueDate] ))

Could you please help me to set up a measure that evaluates all 3 dates involved? Or any other way to do this.

Any light is greatly appreciated.

Desiree

1 ACCEPTED SOLUTION
Super User

@Anonymous - OK, took a closer look, I am thinking:

``````Measure =
VAR __Table =
GENERATE(
'Overdues_DB',
Calendar
),
"Overdue",IF([Date]<[ParkedDate] && [Date]>[ClearingDate]&&[Date]>[NetDueDate]),1,0
)
RETURN
COUNTROWS(FILTER(__Table,[Overdue]=1))

``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

@Anonymous - OK, took a closer look, I am thinking:

``````Measure =
VAR __Table =
GENERATE(
'Overdues_DB',
Calendar
),
"Overdue",IF([Date]<[ParkedDate] && [Date]>[ClearingDate]&&[Date]>[NetDueDate]),1,0
)
RETURN
COUNTROWS(FILTER(__Table,[Overdue]=1))

``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

This worked! Thank you so much!

Super User

@Anonymous - You can use EOMONTH([Date],0) to get the last day of a month. Will have to look a little closer beyond that.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...