Calculate open credit; avoid blank values

[Petanek333]

Hi, 

I am trying to create an accounts receivables report and I want one list to show data about the amount of open credit of various customers, preferably in an area chart. My data look like this:

Basically I want to calculate a running total of the value of invoices, that have been created (invoice date <= selected date) and not yet paid (payment date > selected date or there is yet no payment date).

Best I could do so far is this: 

Open rec =
CALCULATE (
    [Amount],
    FILTER (
        ALLSELECTED ( 'Fact table' ),
        'Fact table'[Customer] = MAX ( 'Fact table'[Customer] )
            && 'Fact table'[Invoice date] <= MAX ( 'Calendar'[Dates] )
            && 'Fact table'[Payment date] > MAX ( 'Calendar'[Dates] )
    )
)

The problem is that the result should look like this 

 but it looks like this with missing values:

So I have three problems/questions:

1) What can I do to fill the blank values with the last non blank value?

2) This sample data include only paid invoices but in real data the Payment date could be blank meaning the invoice is still not paid and this should be reflected in the measure.

3) Can I create two measures - one for receivables due (simply not past the due date) and one for receivables overdue without using a virtual table? I would love to use it in a stacked area chart instead in a normal one.

Sample file attached: File 

Thank you for helping

[Ashish_Mathur]

Hi,

What do you mean by selected date?  Is that a date that you will select in the slicer.  If yes, then which date field in your table should it be related to? - Invoice date, due date or payment date?

[Petanek333]

Hi @Ashish_Mathur ,

selected date is every single date in a calendar table. I would like to have a table (and a stacked area chart) that would show the value of open credit (ideally composed of differentiated due and overdue receivables) for every day. In my model the calendar table is 365 days ending today (24.11.2022 to 24.11.2022).

In theory, it should be related to all of the three mentioned dates. 

If the selected date falls in the interval between invoice date and payment date (or the payment day is blank) it is open credit. If the selected date is past the due date, it is "past due open credit", if the selected date is before the due date, it is "before due open credit".

[aj1973 · Accepted solution]

Hi @Petanek333 

From the measure take out 

'Fact table'[Customer] = MAX ( 'Fact table'[Customer] )

[Petanek333]

Thank you very much.