Calculating Overdues for a time-series

[Anonymous]

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.

[Anonymous]

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]

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]

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

[Anonymous]

@Anonymous thank you for your reply.

I would like the slicers to change the data shown in the report. I've attached a Pbix sample in this reply.

I was calculating the 2015 as if it was 31/12/2015. This is, I was infering that, choosing only the year, it would return the amount for the last day of the year, which explains the 11800€ instead of 15050.5€.

Thank you.

Pbix File 

[Anonymous]

Hi @Anonymous ,

I updated the formula of measure [Overdues] in your sample pbix file, please check whether that is what you want.

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

Best Regards

Rena

[Kevin_Harper]

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?