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 Accounts.  Also there is a Fiscal Period 0 which represents previous year AR Balance. I have a date table.

 

What I would like:

I need a calculation that is Monthly AR Total divided by Monthly Total for Revenue accounts divided by number of days in the month. AR Total/(Rev Total/#of days in month). This gives me my days in AR

I then need to graph that for Number of days in AR per month, and Number of days in AR YTD. 

 

Hear is what the data looks like in excel:

MonthDay in AR MTHDay in AR YTDTarget Day in AR # of DaysAR Balance MthResident Revenue MthResident Revenue Ytd
Sep-19                                  30.70                                  31.40  30              1,134,992.91                                    1,109,293.95                                  13,192,841.34
Aug-19                                  36.78                                  37.64  31              1,357,630.26                                    1,144,421.81                                  12,083,547.39
Jul-19                                  28.40                                  28.91  31              1,040,300.32                                    1,135,412.82                                  10,939,125.58
Jun-19                                  28.57                                  29.23  30              1,049,699.34                                    1,102,262.41                                    9,803,712.76
May-19                                  36.47                                  36.13  31              1,293,904.83                                    1,099,724.37                                    8,701,450.35
Apr-19                                  28.42                                  27.72  30                  993,801.77                                    1,048,982.88                                    7,601,725.98
Mar-19                                  29.87                                  31.15  31              1,121,686.15                                    1,164,071.70                                    6,552,743.10
Feb-19                                  26.42                                  25.50  28                  910,072.66                                        964,316.92                                    5,388,671.40
Jan-19                                  27.02                                  27.75  31                  998,037.52                                    1,145,187.03                                    4,424,354.48
Dec-18                                  24.57                                  24.16  31                  861,260.63                                    1,086,799.25                                    3,279,167.45
Nov-18                                  24.73                                  24.69  30                  887,451.16                                    1,076,769.19                                    2,192,368.20
Oct-18                                  22.88                                  22.88  31                  823,237.94                                    1,115,599.01                                    1,115,599.01

There is a difference between what is in excel and what I can get out of the data.  For period 1(Oct-18) I have a smaller number until I add in the Period 0 (Previous Balance).  Then The totals match up.  I hope this makes sence.  I have beat my head aginst the wall for a week now and Have not gotten what I need.  Any help would graciously apreciated.  

 

Thanks,

Peter

 

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

13 Replies

  • 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