Forum Discussion

Helper IV

6 years ago

Solved

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 1 of Sample Data

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

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

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