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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI petermb72 ,

I add three calculate columns to extract date and calculate month total AR and YTD total AR:

 

Date = 
VAR _path =
    SUBSTITUTE ( [Month], "-", "|" )
RETURN
    DATEVALUE ( PATHITEM ( _path, 1 ) & "/1/" & PATHITEM ( _path, 2 ) )

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

Daily AR of Year =
VAR filtered =
    FILTER ( T3, T3[Date] <= EARLIER ( T3[Date] ) )
RETURN
    CALCULATE ( SUM ( T3[AR Balance Mth] ), filtered )
        / DIVIDE (
            T3[Resident Revenue Ytd],
            CALCULATE ( SUM ( T3[# of Days] ), filtered )
        )
        - CALCULATE (
            SUM ( T3[Daily AR of Month] ),
            FILTER ( T3, T3[Date] < EARLIER ( T3[Date] ) )
        )

 

You can use them to create a line chart to show the graph:

Since I get different results from the snapshot, can you please explain more about YTD AR calculate? 

Regards,

Xiaoxin Sheng

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

the formula for the AR YTD =  AR Balance for the month / (Revenue YTD / # of days YTD)

 

Does this make a difference in your formulas?

 

I am very thankful for your help!  It is people like you that make the beginners like me believe in humanity again!  I hope I can help someone else someday.  If I have any more questions about this, may I contact you?

 

Peter

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

    Also here are my column headings that I am dealing with in the raw data:

    Year, Period ID, Account Type, Balance.  In that same order I have data that looks like:   2019, 0, Accounts Recievable, $3,000.00

     

    I have multiple accounts receivable accounts as well as multiple revenue accounts (ie, Nursing Revenue and Other Revenue, and multiples of each of them).

     

    Period 0 only have totals for AR accounts.  Revenue accounts have periods 1-12.  I am just trying to give you all the info that I can.  There is other fields but I am giving you just the pertinate ones.

     

    Thanks, Peter

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi petermb72 ,

      So you mean most of the AR fields not really existed in your raw table? Can you please share some sample data for test?

      How to Get Your Question Answered Quickly  

      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!