Asked by:
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
All replies


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