Forum Discussion

petermb72's avatar
petermb72
Icon for Helper IV rankHelper 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's avatar
    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

petermb72's avatar
petermb72
Icon for Helper IV rankHelper IV
6 years ago

Can you let me know how I can attach or get you the sample data?

 

Thanks!

Anonymous's avatar
Anonymous
Not applicable
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