I am struggling with calculation of Daily Sales outstanding using the countback method.
This takes current amount outstanding and reduces the amount by sales until the amount is 0. then the difference in days between selected date and the day the balance becomes zero is the DSO.
Initially I am working with a static dax table to get the logic right, but will change the account and dates to MAX once comfortable with logic.
Plan is to generate a table using ADDCOLUMNS
col of last 365 days. (Done)
col for Current balance into all rows (Done)
col to index days in reverse order (Cannot get to work)
col to pick up daily sales balances (Cannot get to work)
Col to run cum total working balance less daily sales with EARLIER on index days
Then Count rows . greater than 0
My Dax code is
ZZ DSO4 =
VAR myDate = CALCULATE(max(‘Calendar’[Date]),‘Calendar’[Date]= Date(2020,3,31))
var myDateRange = DATESINPERIOD(‘Calendar’[Date],mydate,-31,DAY)
Var myAccount = “C0057”
Var myAccountBalance = CALCULATE([AccountBal],FullFile[AccountCode]=“C0057”,FullFile[DebtorDate]<=myDate)
Var mSales = CALCULATE([SumBalance],FILTER(FullFile,FullFile[CDKFile]=“Trans”),FILTER(FullFile,FullFile[DocumentType]<>“P”),FullFile[AccountCode]=myAccount)
var msales2 = CALCULATE(sumx(FullFile,FullFile[Balance]),FILTER(FullFile,FullFile[CDKFile]=“Trans”),FILTER(FullFile,FullFile[DocumentType]<>“P”),FullFile[AccountCode]=myAccount)
Var myTab = ADDCOLUMNS(myDateRange,“mDate”, ‘Calendar’[Date],“Bal”,myAccountBalance)
Var myTab2 = ADDCOLUMNS(myTab,“Index”,RANKX(myTab,‘Calendar’[Date],1,DESC),“DailyTot”,mSales)
The index and daily sales are summing the whole table and not placing the daily sales in the correct row for the day. Looks Like a TREATAS, but I can’t seem to reference the columns gernerated in AddCOLUMNS variable. Also get messages like “col not in base table”
SO firstly - Is this the right approach to calculate DSO - feels right and better than my first attempt.
Secondly - how do I reference the virtual tables to summarise amounts by day?
PBIX also attached - DAX table is ZZ DS04
Thanks DebtorsEDNA.pbix (448.2 KB)