Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating Overdues for a time-series

Good afternoon.

 

I have an excel worksheet with some clients current accounts, that I present below. I want to calculate the total overdue amount, i.e., how much I should have received from my clients that I didn't, for a time-series period (for example, 2014-2018). The goal is to return a bar graph with the year in the x axis and the overdue amount in the y axis.

I've created a calculated column where it returns "On Time" if Expiration Date is higher than Payment Date, "Delayed" otherwise.

 

In my sample, I have two different value columns to sum. If Status is "Yet to Paid", it should return Total Pending, if Payment Date > Expiration Date and Payment Date> Date, it must return Total Received. So, for a given year, there are 3 conditions to return a value:
- The Invoice must be "Delayed";
- The Payment Date must be higher than the date;
- Expiration Date must be smaller than the date.

 

For instance, in 2015 it must return 700+2500+4000+1200+3400= 11800€

 

Can someone help me figuring out the solution?

 

Thanks in advance.

Need Help

6 Replies

  • Kevin_Harper's avatar
    Kevin_Harper
    Icon for Helper I rankHelper I
    6 years ago

    Hello,

    There are 3 situtations that you say should be used to calculate your total:

    1. The Invoice must be "Delayed";

     

    Can you explain further how a payment is "Delayed" or how it comes to be in "Delayed" status?

     

    Also you say:

    2. The Payment Date must be higher than the date;
    3. Expiration Date must be smaller than the date.

     

    You cited that you would like to return 5 results for 2015:

    700 + 2500 + 4000 + 1200 + 3400

     

    Why is the 3100 from Company A not included in the total you are seeking?  Their payment date, 14/04/2015, is greater than the expiration date 26/02/2015.

     

    Also, which date are you referencing here when you say "Payment Date must be higher than the date"  Which date?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    You can create a measure as below:

    Overdues = 
VAR _cinvdate =
    MAX ( 'Table'[Invoice Date] )
VAR _pending =
    CALCULATE (
        SUM ( 'Table'[Total Pending] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] = "Yet to Pay"
                && YEAR ( 'Table'[Invoice Date] ) <= YEAR ( _cinvdate )
        )
    )
VAR _Overdues =
    _pending
        + CALCULATE (
            SUM ( 'Table'[Invoice Total] ),
            FILTER (
                'Table',
                YEAR ( 'Table'[Invoice Date] ) = YEAR ( _cinvdate )
                    && 'Table'[Expiration Date] > 'Table'[Invoice Date]
                    && NOT ( ISBLANK ( 'Table'[Payment Date] ) )
                    && 'Table'[Payment Date] > 'Table'[Expiration Date]
            )
        )
RETURN
    _Overdues

    As you checked the above screenshot, the final result is 15050.5 not 11800(3100+700+2500+150.5+4000+1200+3400) in 2015. Because it include all of the companies except company B and F(their payment date is smaller than expiration date). So I want to know why company A and Company E didn't be included in your formula(700+2500+4000+1200+3400= 11800€)? Is there any other condition missing in my formula?

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your replies Kevin_Harper and Anonymous.

       

      I think I didn't specify enough my situation. I want to get the daily amount for the overdues between a time-period (2014-2018). I was considering 31-12-2015 for 2015, which is not entirely correct. 

      I have year, month and daily filter in my report and I want to give the power to the user to play between dates in a bar graph and choose which value(s) he wants to check.

      For example, if he chooses 31-12-2015, the bar graph must return 11800€. But if he chooses the interval between 29-12-2015 and 31-12-2015, I want the bar graph, with the date in the x axis, to return 11800€ for each day. Here is a snapshot of my filters:

       

       

      I tried your formula but it doesn't fulfill my request. May I try something else?

       

      Thanks in advance.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Do you want to the data in the visual change by the date slicers? And could you please clear my doubt in my previous post?

        It include all of the companies except company B and F base on my formula. Why company A and Company E didn't be included in your formula(700+2500+4000+1200+3400= 11800€)? Is there any other condition missing in my formula?

        It is better that if you can provide your sample pbix file in order to provide you a proper solution. 

        Best Regards

        Rena