Days Sales Outstanding (DSO) and Rolling 12 months Revenue in FDI

Rupali V

I would like to develop FDI table visualization that has DSO using 12 months and Rolling 12 months Revenue using Fiscal Period. Relative time function is not available for Fiscal period. How to calculate DSO and 12 months revenue.

Daniel Ryan

Hi @Rupali V

What Subject Area are you using for this? As far as I am aware, all the OOTB subject areas have a relative time function.

If you create a custom calculation, there is a PERIODROLLING function that might help.

Rupali V

I am using Financials: AR Revenue, AR Transactions.

Daniel Ryan

@Rupali V in that case try the periodrolling function above for your rolling 12 month calculation. In fiscal period there is also a trailing fiscal period column you could use for average using a filter statement where trailing period between 0 and 11.

Rupali V

Yes, I used trailing period number using filter from 0-11. what are corresponding fields from AR Revenue and Transactions to calculate DSO. Please refer if I need to use any other subject area.

Thank you!

Daniel Ryan

@Rupali V in either AR Balance or AR Aging there is an Original Amount field. You could try that.

Rupali V

I am using PERIODROLLING function to get 3 months rolling revenue. I am getting correct values under column 'Total 3 M Rolling Revenue' except for last month Jan 26(Trailing period number=1) getting calculation wrong.

it should sum(revenue amt) for trailing period number =1,2 and 3 but it is skipping trailing period number =3 below is query for calculated field and screenshot with highlighted wrong value (87,418,618.43) . correct value highlighted green. I am using AR Revenue subject area.

CASE
WHEN "Financials - AR Revenue"."Time"."Trailing Period Number" <= 10
THEN PERIODROLLING("Financials - AR Revenue"."Facts - Document Currency"."Revenue Amount", -2, 0,"Financials - AR Revenue"."Time"."Fiscal Calendar")
END