none
DAX Query to calculate Days Sales Outstanding RRS feed

  • 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 T_Ezzy Sunday, August 25, 2019 6:59 PM
    • Proposed as answer by T_Ezzy Sunday, August 25, 2019 6:59 PM
    Sunday, August 25, 2019 6:57 PM