Calculating Days Sales Outstanding (DSO) historical

[hnguy71]

Hi All.

I have quite a challenge and I'm not sure how to proceed. I have a sample dataset that returns me the total billed for the current period and all payments for prior periods (up to 24 months). I then also have another table that stores my balance for each respective period.  The idea is to count backwards and see how long it would take to clear the balance. 

Here's a sample of the DSO table:

PostedDate     Account            Value                   Offset  OffsetDate    Days

12/01/2019	TOTBIL_M	$13,804,541	0	12/31/2019	31
12/01/2019	TOTBIL_M_01	$13,775,181	-1	11/30/2019	30
12/01/2019	TOTBIL_M_02	$13,508,124	-2	10/31/2019	31
12/01/2019	TOTBIL_M_03	$14,758,989	-3	09/30/2019	30
12/01/2019	TOTBIL_M_04	$22,432,463	-4	08/31/2019	31
12/01/2019	TOTBIL_M_05	$16,792,839	-5	07/31/2019	31
12/01/2019	TOTBIL_M_06	$14,739,866	-6	06/30/2019	30
12/01/2019	TOTBIL_M_07	$16,059,744	-7	05/31/2019	31
12/01/2019	TOTBIL_M_08	$17,258,471.63	-8	04/30/2019	30
12/01/2019	TOTBIL_M_09	$16,539,435.4	-9	03/31/2019	31
12/01/2019	TOTBIL_M_10	$17,423,565.15	-10	02/28/2019	28
12/01/2019	TOTBIL_M_11	$20,244,143.57	-11	01/31/2019	31
12/01/2019	TOTBIL_M_12	$17,862,394.82	-12	12/31/2018	31
12/01/2019	TOTBIL_M_13	$17,070,963.62	-13	11/30/2018	30
12/01/2019	TOTBIL_M_14	$19,914,972.9	-14	10/31/2018	31
12/01/2019	TOTBIL_M_15	$17,279,826.09	-15	09/30/2018	30
12/01/2019	TOTBIL_M_16	$22,278,329.97	-16	08/31/2018	31
12/01/2019	TOTBIL_M_17	$19,736,538.17	-17	07/31/2018	31
12/01/2019	TOTBIL_M_18	$20,112,045.52	-18	06/30/2018	30
12/01/2019	TOTBIL_M_19	$18,488,243.3	-19	05/31/2018	31
12/01/2019	TOTBIL_M_20	$14,345,520.47	-20	04/30/2018	30
12/01/2019	TOTBIL_M_21	$15,695,651.51	-21	03/31/2018	31
12/01/2019	TOTBIL_M_22	$15,038,192.85	-22	02/28/2018	28
12/01/2019	TOTBIL_M_23	$17,446,928.09	-23	01/31/2018	31
12/01/2019	TOTBIL_M_24	$15,419,503.28	-24	12/31/2017	31
11/01/2019	TOTBIL_M	$13,775,181	0	11/30/2019	30
11/01/2019	TOTBIL_M_01	$13,508,124	-1	10/31/2019	31
11/01/2019	TOTBIL_M_02	$14,758,989	-2	09/30/2019	30
11/01/2019	TOTBIL_M_03	$22,432,463	-3	08/31/2019	31
11/01/2019	TOTBIL_M_04	$16,792,839	-4	07/31/2019	31
11/01/2019	TOTBIL_M_05	$14,739,866	-5	06/30/2019	30
11/01/2019	TOTBIL_M_06	$16,059,744	-6	05/31/2019	31
11/01/2019	TOTBIL_M_07	$17,258,471.63	-7	04/30/2019	30
11/01/2019	TOTBIL_M_08	$16,539,435.4	-8	03/31/2019	31
11/01/2019	TOTBIL_M_09	$17,423,565.15	-9	02/28/2019	28
11/01/2019	TOTBIL_M_10	$20,244,143.57	-10	01/31/2019	31
11/01/2019	TOTBIL_M_11	$17,862,394.82	-11	12/31/2018	31
11/01/2019	TOTBIL_M_12	$17,070,963.62	-12	11/30/2018	30
11/01/2019	TOTBIL_M_13	$19,914,972.9	-13	10/31/2018	31
11/01/2019	TOTBIL_M_14	$17,279,826.09	-14	09/30/2018	30
11/01/2019	TOTBIL_M_15	$22,278,329.97	-15	08/31/2018	31
11/01/2019	TOTBIL_M_16	$19,736,538.17	-16	07/31/2018	31
11/01/2019	TOTBIL_M_17	$20,112,045.52	-17	06/30/2018	30
11/01/2019	TOTBIL_M_18	$18,488,243.3	-18	05/31/2018	31
11/01/2019	TOTBIL_M_19	$14,345,520.47	-19	04/30/2018	30
11/01/2019	TOTBIL_M_20	$15,695,651.51	-20	03/31/2018	31
11/01/2019	TOTBIL_M_21	$15,038,192.85	-21	02/28/2018	28
11/01/2019	TOTBIL_M_22	$17,446,928.09	-22	01/31/2018	31
11/01/2019	TOTBIL_M_23	$15,419,503.28	-23	12/31/2017	31
11/01/2019	TOTBIL_M_24	$17,218,360.07	-24	11/30/2017	30

Here's a sample of my balance table:

Date                   Balance

12/01/2019	$30,901,690.63
11/01/2019	$30,348,830.63

As you can see it for November it takes about a little over 2 months to equal or become greater than the balance. I takes about 6.23 days into month 3. The estimated DSO for that balance would then come out to 67.23 (31 + 30 + 6.23):

Using practically the same data for December, we can see that it takes about 3 months to clear as well:

This time, it takes around 7.62 days from the 3rd month to clear the balance which gives us a total of 68.62 (30 + 31 + 7.62)

@Ashish_Mathur , I believe you had a similar post on your blog. If possible, could you help me solve this? I have included a pbix with sample data for anyone to play around with. 

Thanks in advanced!

PBIX: https://1drv.ms/u/s!An8CCFsOzw0ug02Bwwp417v2Xf-P?e=SFaB6E

[LifeLongLearner]

@hnguy71 can u pls share the pbix file.

Thanks in Advance. 

[hnguy71 · Accepted solution]

Turns out I was able to figure it out using a round-about method but it works. I'll keep this thread open for a few days if anyone has a better solution:

[Anonymous]

Can you please share the PBIX file where you implemented DSO calculation.