Forum Discussion

Tamuna's avatar
Tamuna
Frequent Visitor
4 years ago

Debt Aging

Hello,

 

I have Sales Table , that shows date, sold product name, price and customer name, (data has millions of rows)

Returned goods Table that shows date, customer name and product name

Payment Table, that shows payment date, payment type (is it cash payment or cash backs that company gives to client ) customer name and amount. (data has millions of rows)

 

also I have different number of days in wich customer should pay the debt.

 

the cutomer may pay partialy for already bought item, or may pay in advance.

 

I have measure that counts debts in selected day.

 

I need to build table, that shows every selected day : debts that are not overdue, overdue debt, and overdue debt in days 

  1-30   31-60   61-90   91-180  181+ 

 

please help,

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Tamuna , Have already figured out the logic for debt in days measure ? if not

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    If yes, then you need an independent date table, and using the level of customer or invoice you can calculate the dynamic bucket

     

    refer

    Dynamic Segmentation Bucketing Binning
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626


    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

  • Tamuna's avatar
    Tamuna
    Frequent Visitor
    4 years ago

    Unfortunatelly sample data language is not into English. The source is SQL Server. I tried to visualize what I ment.

    Hope its more understandable.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Based on the data you provided, what are these calculation logic?

     

    • Tamuna's avatar
      Tamuna
      Frequent Visitor
      4 years ago

      Debts on time - Debts that are not overdue, client has time to pay.

      I have different days of payment for each client as a table and also in a sale table there is a column that shows a day, before that client should pay.

      Overdue Debts - Debts that are Over that payment day,  amounts  that are overdue.

      1- 30 - Debts that are overdue from 1 to 30 days. (count of days begings after a day in which client should pay)

      31-60 - Debts that are overdue from 31 to 60 days. 

      91-180 - Debts that are overdue from 91 to 180 days. 

      180+ - Debts that are overdue 180+ days.