How create Month end evolution logic

• Data Structure: Ensure your data model has the following tables:

Vendor Table: Contains the Vendor ID, Posting Date, Clearing Date, and Net Due Date.
Date Table: A comprehensive date table with a continuous range of dates. Include columns for year, month, day, and month end dates.

• You can create two relationships between the Date table and the Vendor table:
  • One based on Posting Date (to capture the month of posting).
  • Another based on Clearing Date (to capture the month of clearing).
• Inactive Relationships: If you have a date table connected to both dates, ensure that one relationship is inactive to avoid ambiguity. You can use USERELATIONSHIP in your DAX formulas to activate the inactive relationship when needed.

• Not Due Count = 
  CALCULATE(
  COUNTROWS(Vendor),
  Vendor[Net Due Date] > EOMONTH(TODAY(), -1)
  )
  
  Age 0-5 Days = 
  CALCULATE(
  COUNTROWS(Vendor),
  Vendor[Net Due Date] <= EOMONTH(TODAY(), -1) + 5 && 
  Vendor[Net Due Date] > EOMONTH(TODAY(), -1)
  )
  
  Age 6-15 Days = 
  CALCULATE(
  COUNTROWS(Vendor),
  Vendor[Net Due Date] <= EOMONTH(TODAY(), -1) + 15 && 
  Vendor[Net Due Date] > EOMONTH(TODAY(), -1) + 5
  )
  
  Age 16-30 Days = 
  CALCULATE(
  COUNTROWS(Vendor),
  Vendor[Net Due Date] <= EOMONTH(TODAY(), -1) + 30 && 
  Vendor[Net Due Date] > EOMONTH(TODAY(), -1) + 15
  )