DSO DAys, from different tables

[iimlromero]

Hello Imam tring to do the following calculation but still cant.

1)  Fact Table with sales (day, invoice number, ammount, customer, etc...)

2) Table with Customer debt for each friday.

I would like to calculate this for each available debt in the second table:

DaysOfDebt = Debt x 60 / Cummulated Invoiced ammount of last 60 days.

I hope I was clear in what i am tring to do.

Thanks, and excuse my poor english.

Max

[technolog · Accepted solution]

To calculate the Days Sales Outstanding (DSO) based on the sales data and the customer debt data for each Friday, you can follow these steps in DAX:

Step 1: Create a Measure for Cumulative Invoiced Amount of Last 60 Days
First, create a measure that calculates the cumulative invoiced amount over the last 60 days:

CumulativeInvoiceLast60Days =
CALCULATE(
SUM('BaseFacturacion'[Cantidad]),
FILTER(
ALL('BaseFacturacion'),
'BaseFacturacion'[Fecha] >= MAX('BaseFacturacion'[Fecha]) - 60 &&
'BaseFacturacion'[Fecha] <= MAX('BaseFacturacion'[Fecha])
)
)
Step 2: Create a Measure for Debt
Next, create a measure for the debt from the CuentaCorriente table:

DebtAmount = SUM('CuentaCorriente'[Deuda ($)])
Step 3: Create the DSO Measure
Now, combine the cumulative invoiced amount and the debt to calculate the DSO:

DaysOfDebt =
IF(
[CumulativeInvoiceLast60Days] <> 0,
[DebtAmount] * 60 / [CumulativeInvoiceLast60Days],
BLANK()
)