Forum Discussion

eryka_90's avatar
eryka_90
Helper I
1 year ago

How create Month end evolution logic

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

Need Help

5 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    1 year ago

    eryka_90 

     

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

     

    Regards

     

    Phil

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    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's avatar
      eryka_90
      Helper I
      1 year ago

      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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago
    • 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
      )