How create Month end evolution logic

[eryka_90]

Hi all, 

I'm trying to figure out how to visualize based on below condition.

Condition:

Jan 2024 : Posting date < Feb 1st 2024 and clearing date > Jan 31st  => Open: 200,000 invoice/$450 / Debit Balance : 500 vendors / $250

Feb 2024 : Posting date < Mac 1st 2024 and clearing date > Feb 28st  => Open: 180,000 invoice/$400 / Debit Balance : 300 vendors / $150

Mac 2024 : Posting date <Apr 1st 2024 and clearing date > Mac 31st  => Open: 210,000 invoice/$550 / Debit Balance : 400 vendors / $280

Example

Cora ID 123

Posting Date 15 jan 2024

Clearing date 15 Mar 2024

Net due date 5 Feb 2024

Ageing is compare due date of the last day of the month. (In Jan, last date is 31st)

Expected result

Month	Not Due	0-5 Days	6-15 Days	16-30 Days
Jan	1	0	0	0
Feb	0	1	0	0
Mar	0	0	0	0

My question is

1. how to calculate the value to meet condition
2. Which date should be used to link between date table and Vendor table? If use posting date, the month will be based on posting date meanwhile the actual is combination of posting date and clearing date

TQVM

[Kedar_Pande]

• 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
  )

[suparnababu8]

Hi @eryka_90  

First you have to create Calender table. The you have to link this Calender table to vendor table by using Posting date 

Then Calculate Ageing bins 

Ageing Bins = 
VAR DueDate = 'Vendor'[Net Due Date]
VAR LastDayOfMonth = EOMONTH(DueDate, 0)
VAR DaysDifference = DATEDIFF(DueDate, LastDayOfMonth, DAY)
RETURN
    SWITCH(TRUE(),
        DaysDifference <= 0, "Not Due",
        DaysDifference <= 5, "0-5 Days",
        DaysDifference <= 15, "6-15 Days",
        DaysDifference <= 30, "16-30 Days",
        "Over 30 Days"
    )

After this create Open Invoice mesure  

Open Invoices = 
CALCULATE(COUNTROWS('Vendor'),
    FILTER('Vendor',
        'Vendor'[Posting Date] < DATE(2024, 2, 1) &&
        'Vendor'[Clearing Date] > DATE(2024, 1, 31)))

After this create Debit balance measure 

Debit Balance = 
CALCULATE(SUM('Vendor'[Debit Balance]),
    FILTER('Vendor',
        'Vendor'[Posting Date] < DATE(2024, 2, 1) &&
        'Vendor'[Clearing Date] > DATE(2024, 1, 31))
)

The take Matrix chart and drag month column from calender table in to rows, drag Ageing Bins column into columns and last but not least drag Open Invocie and Debit balance into values.

Let me Know if it works...

[eryka_90]

Hi @suparnababu8 ,

Cant figure out how open invoice and debit balance worked. Could you explained more detail?

I have attached the sample data in the comment.

TQVM

[PhilipTreacy]

@eryka_90 

Please provide sample data in a usable format (table or file) not a picture.

Regards

Phil

[eryka_90]

HI @PhilipTreacy 

You can refer to the sample data here Sample Data Vendor 

TQVM