# DAX Query to calculate Days Sales Outstanding

• ### Question

• I am having a problem with a DAX calculation in a tabular model I have set up in Analysis Services

I have a table of debts and a list of sales and I want to calculate DSO using the count back method.   All my sales and debts are tagged with the first day of the month to which they belong.   For example:

 Total Debt 1st April 2013 £80 Sales Debt 1st Apil 2013 £25 25 55 Reduced by 25 30 Days in April 1st March 2013 £30 30 25 Reduced by 30 31 Days in March 1st  February 2013 £20 20 5 Reduced by 20 28 Days in February 1st Jan 2013 £40 40 0 Fully paid this month - reduced by 5 3.875 Days in January Total DSO (30+31+28+3.875)= 92.875 Calculation for January - debt fully paid off this month - but as we did not need all the sales for the month - need to calculate how many days sales this represents: Sales in January 40 Days in January 31 Sales per day January 1.29 i.e 40/31 Debt paid off in January 5 Days equivalent 3.875 i.e. 5/1.29

I have been able to do this as SQL query – and my first though was to build a new table that calculated the DSO and held it as a number of days.   However this does not work as I need to be able to calculate the DSO on the fly as I need to be able to view this at a company, divisional and departmental level.   Ideally I would like to set this up as a calculated measure in my model so that it can be exposed to presentation tools such as powerpivot, powerview and performance point.   Has anyone done anything similar?   I have been banging my head off a brick wall with this one so any help would be greatly appreciated

Many Thanks

Thursday, April 18, 2013 8:54 AM

### All replies

• Shame no body replied to this.. Did you find a way of doing it? I need to do this very thing!

Friday, March 22, 2019 3:30 PM
• I have used a function that calculates available stock based on fifo as a basis to calculate DSO using the count back method. Also, used a date dimension table to help with the query.

```WITH CTE_LDOM
AS
(
Select LastDayOfPreviousMonth From Master.dbo.DateDimension D Where  D.Date = CAST(GETDATE() as Date)
),
CTE_Orders
AS
(
Select  1 AS RN, Outstanding as Quantity, 'Debtors' as Item From [Table] L
where [date] <= (Select LastDayOfPreviousMonth From CTE_LDOM)
),
CTE_Inventory AS
(
Select ROW_NUMBER() OVER (ORDER BY D.LastDayOfMonth desc) AS RN, D.MMYYYY as Item, S.NetSales, DateDiff(d,D.FirstDayOfMonth, D.LastDayOfMonth) + 1 as DaysinMonth
From ReportsAAB.dbo.DateDimension D
Inner Join (Select D.MMYYYY, NetSales From [Table] L) S on S.MMYYYY = D.MMYYYY

),
CTE AS
(
SELECT  o.RN AS OrderRN,
inv.RN AS InvRN,
inv.Item,
o.Quantity AS OrderedQuantity ,
inv.NetSales AS InvQuantity,
inv.DaysinMonth,
CASE WHEN inv.NetSales - o.Quantity > 0 THEN o.Quantity  ELSE inv.NetSales END AS ServedQuantity ,
CASE WHEN inv.NetSales - o.Quantity > 0 THEN 0 ELSE o.Quantity - inv.NetSales END AS LeftToServe,
CASE WHEN inv.NetSales - o.Quantity > 0 THEN inv.NetSales - o.Quantity ELSE 0 END AS LeftInLot
FROM CTE_Orders o
Outer Apply CTE_Inventory inv --ON o.Item = inv.Item
WHERE o.RN =1 AND inv.RN = 1

UNION ALL

SELECT  CASE WHEN c1.LeftInLot <=0 THEN c1.OrderRN ELSE c2.OrderRN END AS OrderRN
,CASE WHEN c1.LeftInLot <=0 THEN c2.InvRN ELSE c1.InvRN END AS InvRN
,CASE WHEN c1.LeftInLot <=0 THEN c2.Item ELSE c1.Item END AS Item
,CASE WHEN c1.LeftInLot <=0 THEN c1.OrderedQuantity ELSE  c2.OrderedQuantity END AS OrderedQuantity
,CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END AS InvQuantity
,CASE WHEN c1.LeftInLot <=0 THEN c2.DaysinMonth ELSE c1.DaysinMonth END AS DaysinMonth
,CASE WHEN CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END - CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE c2.OrderedQuantity END > 0
THEN CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE c2.OrderedQuantity END
ELSE CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END
END AS ServedQuantity
,CASE WHEN CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END - CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE c2.OrderedQuantity END > 0
THEN 0
ELSE CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE  c2.OrderedQuantity END - CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END
END AS LeftToServe
,CASE WHEN CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END - CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE c2.OrderedQuantity END > 0
THEN  CASE WHEN c1.LeftInLot <=0 THEN c2.InvQuantity ELSE c1.LeftInLot END - CASE WHEN c1.LeftInLot <=0 THEN c1.LeftToServe ELSE  c2.OrderedQuantity END
ELSE 0
END AS LeftInLot
FROM CTE c1
INNER JOIN
(
SELECT  o2.RN AS OrderRN,
inv2.RN AS InvRN,
inv2.Item ,
inv2.NetSales AS InvQuantity,
o2.Quantity AS OrderedQuantity,
inv2.DaysinMonth
FROM
CTE_Inventory inv2
Outer Apply CTE_Orders o2
) c2
ON
((c2.InvRN = c1.InvRN + 1 AND c2.OrderRN = c1.OrderRN AND c1.LeftInLot <= 0 ) OR
(c2.OrderRN = c1.OrderRN + 1 AND c2.InvRN = c1.InvRN AND c1.LeftInLot>0))

)
SELECT  Item, InvQuantity as NetSales, OrderedQuantity as Debtors, ServedQuantity as NetSalesAllocated, LeftToServe as Balance,
(CASE WHEN LeftinLot = 0 then DaysinMonth Else ROUND(ServedQuantity / InvQuantity * DaysInMonth,0) END) as DSO
FROM CTE;```

T Ezzy

• Edited by Sunday, August 25, 2019 6:59 PM
• Proposed as answer by Sunday, August 25, 2019 6:59 PM
Sunday, August 25, 2019 6:57 PM