Measure for overdue invoices report

[Anonymous]

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                                                                     

[Greg_Deckler · Accepted solution]

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

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

[Anonymous]

This worked! Thank you so much!

[Greg_Deckler]

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