Manipulating data to get a Days in AR Calculation

Here is the basics of what I have and what I need: I have a table with Fiscal Period Number(1=Oct), Fiscal Year, Monthly Balances for Multiple AR accounts, Monthly Balances for Multiple Revenue Acco...

Need Help

Anonymous

6 years ago

Hi petermb72 ,

Please check below sample file if it is suitable for your requirement.

Formulas:

AR Balance Mth =
CALCULATE (
    SUM ( Sheet1[NetIncome] ),
    FILTER (
        Sheet1,
        [Fiscal Year] = EARLIER ( Sheet1[Fiscal Year] )
            && [Fiscal Period] = EARLIER ( Sheet1[Fiscal Period] )
            && [CalMonth] = EARLIER ( Sheet1[CalMonth] )
    )
)

Resident Revenue Mth =
CALCULATE (
    SUM ( Sheet1[NetIncome] ),
    FILTER (
        Sheet1,
        [AccountCat] <> "AR"
            && [Fiscal Year] = EARLIER ( Sheet1[Fiscal Year] )
            && [Fiscal Period] = EARLIER ( Sheet1[Fiscal Period] )
            && [CalMonth] = EARLIER ( Sheet1[CalMonth] )
    )
)

Resident Revenue YTD =
CALCULATE (
    SUM ( Sheet1[NetIncome] ),
    FILTER (
        Sheet1,
        [AccountCat] <> "AR"
            && [Fiscal Year] = EARLIER ( Sheet1[Fiscal Year] )
            && [Fiscal Period] <= EARLIER ( Sheet1[Fiscal Period] )
    )
)

Daily AR of Month = 
[AR Balance Mth]/([Resident Revenue Mth]/[DayinMonth])

Daily AR of Year = 
[AR Balance Mth]
    / (
        [Resident Revenue YTD]
            / SUMX (
                SUMMARIZE (
                    FILTER (
                        Sheet1,
                        [Fiscal Year] = EARLIER ( Sheet1[Fiscal Year] )
                            && [Fiscal Period] <= EARLIER ( [Fiscal Period] )
                    ),
                    [Fiscal Year],
                    [Fiscal Period],
                    [CalMonth],
                    [DayinMonth]
                ),
                [DayinMonth]
            )
    )

Date = 
   IF([CalMonth]<>BLANK(), DATEVALUE ( [Fiscal Period] & "/1/" & [Fiscal Year] ))

Regards,

Xiaoxin Sheng

AR sample.zip73 KB

petermb72

Helper IV

6 years ago

Part 2 of Sample Data

Acct #	AccountCat
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1145	AR
1145	AR
1145	AR
1145	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4240	Ancil. Rev
4240	Ancil. Rev
4240	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev

petermb72

Helper IV

6 years ago

Part 2 sample data (column 3 and 4)

Acct#	AccountCat
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1110	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1115	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1120	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1125	AR
1145	AR
1145	AR
1145	AR
1145	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
1170	AR
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4010	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4020	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4028	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4030	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4090	Nursing
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4110	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4120	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4124	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4130	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4140	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4220	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4230	Ancil. Rev
4240	Ancil. Rev
4240	Ancil. Rev
4240	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev
4290	Ancil. Rev

petermb72

Helper IV

6 years ago

Part 3 (column 5,6)

NetIncome	DayinMonth
$195,875.35
($29,015.77)	31
$79,591.90	31
$109,911.51	31
$23,909.11	30
$32,241.32	30
$5,689.19	31
$4,862.84	31
($59,207.21)	28
$58,015.48	31
($38,389.66)	30
$56,768.57	31
($60,949.78)	30
$498,346.14
($19,568.66)	31
$21,806.06	31
$127,595.98	31
($120,389.68)	30
$6,747.94	30
($13,212.39)	31
$53,316.12	31
$1,373.45	28
$54,096.14	31
($72,667.74)	30
($7,334.84)	31
($21,456.97)	30
$169,272.74
$49,416.13	31
($117,840.21)	31
$78,008.71	31
($94,166.49)	30
$18,991.64	30
($32,355.18)	31
$74,581.37	31
($24,626.04)	28
$94,786.64	31
($28,339.78)	30
$41,499.22	31
$25,024.71	30
$13,542.28
($4,714.16)	31
$7,043.23	31
$1,813.74	31
($222.51)	30
$6,232.32	30
$3,908.06	31
($168.37)	31
($5,505.06)	28
($714.99)	31
($894.77)	30
$3,870.11	31
$362.95	30
$0	31
$18,232.22	30
$205,300	31
($205,300)	30
($50,000)
$83.89	31
$0	31
($50,000)	30
$9,779.79	31
$4,184.93	31
$5,430.22	31
$12,407.57	30
$18,113.60	30
$451,743.10	31
$446,472.82	31
$449,207.70	31
$426,577.38	30
$439,561.16	30
$444,726.42	31
$475,007.63	31
$479,865.90	28
$473,135.25	31
$466,857.69	30
$467,182.83	31
$485,501.24	30
$408,726.35	31
$418,473.82	31
$390,924.98	31
$435,414.22	30
$360,362.77	30
$393,408.61	31
$348,812.85	31
$248,305.25	28
$329,061.22	31
$296,304.21	30
$308,628.88	31
$303,260.07	30
$46,008.59	31
$48,714.70	31
$64,345.41	31
$82,583.48	30
$48,649.95	30
$44,201.15	31
$49,829.90	31
$22,043	28
$24,344.23	31
$31,338.73	30
$28,642.29	31
$21,305.86	30
$178,651.71	31
$191,425.50	31
$210,457.87	31
$139,584.94	30
$190,908.19	30
$186,114.78	31
$196,865.12	31
$199,853.54	28
$298,920.55	31
$236,549.02	30
$266,060.72	31
$268,032.60	30
($72,471.50)	31
($123,082.96)	31
($94,149.83)	31
($54,776.83)	30
($87,151.88)	30
($97,433.14)	31
($59,303.67)	31
($126,094.36)	28
($141,115.13)	31
($122,563.98)	30
($149,118.57)	31
($120,440.31)	30
($89.84)	31
$59.04	30
($10)	31
($15)	31
($30)	30
$0.82	31
($20.34)	31
$14.48	31
$969.73	30
($143.66)	31
$79.58	31
($128.52)	28
$916.73	31
$1,603.70	30
($73.54)	30
($1,980.62)	30
$108.71	31
($1,221.25)	30
$79,072.75	31
$85,928.47	31
$82,536.94	31
$56,899.34	30
$88,931.05	30
$74,401.05	31
$100,425.77	31
$93,913.78	28
$125,191.82	31
$98,548.14	30
$123,980.03	31
$103,513.89	30
$7,563.19	31
$23,692.95	31
$17,139.63	31
$16,162.21	30
$14,032.81	30
$16,266.61	31
$19,906.82	31
$9,697.05	28
$9,309.45	31
$8,475.31	30
$13,256.91	31
$12,893.52	30
$147	31
($147)	31
$2,004.44	31
($112.41)	30
$14,191.46	31
$47,445.91	31
$26,417.12	31
$8,502.31	30
$24,262.34	30
$26,940.99	31
$17,357.19	31
$38,695.67	28
$46,552.81	31
$36,891.34	30
$39,795.38	31
$30,169.97	30
$3,386.43	31
($364.32)	30
$52.41	31
($1,252.73)	31
($3,583.03)	31
($2,458.01)	31
($2,681.87)	30
($2,349.34)	30
($1,872.97)	31
($3,632.16)	31
($1,834.39)	28
($1,328.50)	31
($1,406.96)	30
($1,734.80)	31
($2,244.47)	30

Fiscal Year	Fiscal Period
2019	0
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	0
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	0
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	0
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	12
2019	8
2019	9
2019	0
2019	1
2019	10
2019	12
2019	3
2019	4
2019	6
2019	7
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	10
2019	12
2019	3
2019	4
2019	7
2019	8
2019	1
2019	10
2019	12
2019	3
2019	4
2019	5
2019	8
2019	9
2019	2
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	3
2019	4
2019	8
2019	9
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9
2019	1
2019	2
2019	3
2019	1
2019	10
2019	11
2019	12
2019	2
2019	3
2019	4
2019	5
2019	6
2019	7
2019	8
2019	9

Forum Discussion

Manipulating data to get a Days in AR Calculation

Related content

Stored Procedure to Manipulate Tables

Manipulate Data

Basic manipulation of data from events

Data manipulation

Data Manipulation