Home Forums Power Pivot DSO Calculation for Dashboard

Tagged: , , ,

This topic contains 10 replies, has 2 voices, and was last updated by  jwysocki 2 months ago.

Viewing 11 posts - 1 through 11 (of 11 total)
  • Author
    Posts
  • #9981

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    I’m trying to create a dashboard with Power Pivot for our AR team. I’m relatively new to Power Pivot, but I think everything I’m trying to display through the dashboard should be achievable through the use of Power Pivot, however I’m having trouble coming up with a calculation for DSO. I tried to follow https://powerpivotpro.com/2012/04/aged-debtorsdynamic-banding-in-dax/ in creating aged buckets (which didn’t quite work out properly). My thought process behind this was that I’d need to be able to calculate the age of an invoice based upon a slicer, but I realized this probably isn’t the best method to go about it.

    Right now I have a list of line items for each customer that includes fields like amount, invoice date, due date, date paid etc. I also have created a measure for calculating sales.

    Has anyone created a calculated measure to generate a DSO calculation based on a date chosen by a slicer? Any help walking me through creating a measure capable of doing this would be appreciated.

    #9982

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    What version of Excel are you working with?

    I have worked with aging receivables and sales, but not particularly with DSO (days sales outstanding), but I think we could work that out.

    If your invoice info has date and date paid, you should be able to calculate the age of any invoice from a point in time (such as date of data refresh) and not need a slicer. A slicer could be useful if you wanted to filter invoices for a particular date range like current, less than 30, 31-60, etc.

    Also, if you wanted to know for any date period what were the outstanding receivables and sales, the formula can be tricky the first time encountered, but has actually been already solved.

    Can you put up some sample data with the formulas you have been working with?

    #9986

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    I’m using Excel 2016.

    I recreated & attached sub sample of my information with the formulas replicated. On the worksheet named Dashboard I have the pivot table showing my aged “buckets” for the selected customer’s invoices. The pivot chart visualizes this data. I was hoping to have a primary worksheet that displays a few charts with metrics such as receivables by age, top 10 delinquent customers, etc. Additionally, I wanted secondary worksheets that the AR team can select to view a more detailed breakdown like the pivot table of the aging for each customer (at any given point in time), their DSO for the selected time period, sales, etc.

    The measures named Debtor_Value & Aged_Debtor are what I came up with following the guide from the link in my previous post. This properly put values into the buckets I defined based upon due date, however, it fails to exclude invoices that have been paid (cleared) & invoices that wouldn’t have been entered in the system at the point in time selected. I’m still working on figuring out how to fix this. The worksheet named Actual shows what the aging buckets for this customer should look like for 1/26/2018.

    Attachments:
    You must be logged in to view attached files.
    #9995

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    OK.

    Please see attached workbook.

    Let’s tackle Debtor Value for the selected date first. Given your data model, and that you are using Excel 2016, one solution is:

    Debtor_Value:=IF (
        HASONEVALUE ( Clearing_Calendar[Month_End_Date] )
            && HASONEVALUE ( Bands[Band_code] ),
        VAR vDataDate =
            VALUES ( Clearing_Calendar[Month_End_Date] )
        VAR vDaysLowerLimit =
            VALUES ( Bands[From] )
        VAR vDaysUpperLimit =
            VALUES ( Bands[To] )
        RETURN
            CALCULATE (
                [Total_Invoices],
                FILTER (
                    ALL ( Data ),
                    VAR vDaysPastDue = 1.0 * ( vDataDate - [Net Due Date] )
                    RETURN
                        Data[Document Date] <= vDataDate
                            && Data[Clearing Date] >= vDataDate
                            && vDaysPastDue >= vDaysLowerLimit
                            && vDaysPastDue <= vDaysUpperLimit                        
                )
            )
    )
    Attachments:
    You must be logged in to view attached files.
    #10013

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    So I tried loading this measure into my file with more than 1 customer’s data included. It correctly places my invoices in the buckets, however I can no longer see a breakdown by customer, industry, etc.

    I added two more customer line items to the data set so you can see what I’m talking about. Please take a look at the attached.

     

    Attachments:
    You must be logged in to view attached files.
    #10016

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    See attached workbook.

    For fields in the data table that you want to also filter on, add field references (column names) to the ALLEXCEPT() function, as Customer Account Number.

    Debtor_Value:=IF (
        HASONEVALUE ( Clearing_Calendar[Month_End_Date] )
            && HASONEVALUE ( Bands[Band_code] ),
        VAR vDataDate =
            VALUES ( Clearing_Calendar[Month_End_Date] )
        VAR vDaysLowerLimit =
            VALUES ( Bands[From] )
        VAR vDaysUpperLimit =
            VALUES ( Bands[To] )
        RETURN
            CALCULATE (
                [Total_Invoices],
                FILTER (
                    ALLEXCEPT ( Data, Data[Customer Account Number] ),
                    Data[Document Type Description] = "Invoice" &&
                    VAR vDaysPastDue = 1.0 * ( vDataDate - [Net Due Date] )
                    RETURN
                        Data[Document Date] <= vDataDate
                            && Data[Clearing Date] >= vDataDate
                            && vDaysPastDue >= vDaysLowerLimit
                            && vDaysPastDue <= vDaysUpperLimit                        
                )
            )
    )
    Attachments:
    You must be logged in to view attached files.
    #10027

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    That seemed to allow me to do what I was trying to, however I tried to verify some of the outputs and noticed some discrepancies.

    I realized part of my problem was how my aging buckets were structured vs what I was trying to portray. Once I fixed that, the amounts calculated by power pivot seemed cleaner, however I still found some issues.

    I uploaded the last workbook you sent except with more data. The line for customer #48005 is calculating a number that I can’t seem to replicate. It’s placing more into the Current & 0-20 buckets than expected. This isn’t the only customer who was doing this.

    I appreciate your patience and willingness to help. I’m trying to follow along and make sense of the measures as I see them, however I still am learning.

     

    Attachments:
    You must be logged in to view attached files.
    #10029

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    OK. This one should be easy to tweak. My previous calculation was assuming that if a payment was made, it was made at the end of the day, and your results are based on when a payment is made, it is treated as if the money were received at the beginning of the day. The formula below changes a >= to a >.

    Debtor_Value:=IF (
        HASONEVALUE ( Clearing_Calendar[Month_End_Date] )
            && HASONEVALUE ( Bands[Band_code] ),
        VAR vDataDate =
            VALUES ( Clearing_Calendar[Month_End_Date] )
        VAR vDaysLowerLimit =
            VALUES ( Bands[From] )
        VAR vDaysUpperLimit =
            VALUES ( Bands[To] )
        RETURN
            CALCULATE (
                [Total_Invoices],
                FILTER (
                    ALLEXCEPT ( Data, Data[Customer Account Number] ),
                    Data[Document Type Description] = "Invoice" &&
                    VAR vDaysPastDue = 1.0 * ( vDataDate - [Net Due Date] )
                    RETURN
                        Data[Document Date] <= vDataDate
                            && Data[Clearing Date] > vDataDate
                            && vDaysPastDue >= vDaysLowerLimit
                            && vDaysPastDue <= vDaysUpperLimit                        
                )
            )
    )
    Attachments:
    You must be logged in to view attached files.
    #10065

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    That made a lot of sense, I’m not sure how I was missing that. I decided to do a more thorough check to make sure things were adding up but I ran into another issue.

    Any invoice that has not been paid does not have a clearing date. As a result, some of my lines in the Data[Clearing Date] column have  a value of “00/00/0000.” I think the measure is skipping over these values. Based upon when I pulled the data vs the period I’ve been testing with, it would make sense that I wouldn’t have noticed it right away.

    I think if I just added an OR statement to formula so it read along the lines of Data[Clearing Date] > vDataDate || Data[Clearing Date] = 00/00/0000, I think I would fix this. My attempts to insert this into the formula have not been successful.

    Do you think this is the right course of action?

    #10068

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    Even though the original data shows a value of 00/00/0000, it appears as a null / blank value.

    #10072

    jwysocki
    Participant
    • Started: 1
    • Replies: 6
    • Total: 7

    I’m finally okay with how the Debtor_Value is working. I ended up duplicating my Data[Clearing Document] column and manipulating it to be either contain an “O” or “C” for “open” or “cleared.” I then changed the formula to include (Data[Clearing Date] > vDataDate || Data[O/C Indic.] = “O”).

    This seemed to resolve my last issue. It might not be the most efficient way to do this, but it worked.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.