Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Petanek333
Helper III
Helper III

Calculate open credit; avoid blank values

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:

Petanek333_0-1669251118498.png

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 

Petanek333_1-1669251372531.png

 but it looks like this with missing values:

Petanek333_2-1669251403999.png

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

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

Hi @Petanek333 

From the measure take out 

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

aj1973_2-1669253849133.png

 

aj1973_0-1669253656861.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Community Champion
Community Champion

Hi @Petanek333 

From the measure take out 

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

aj1973_2-1669253849133.png

 

aj1973_0-1669253656861.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Thank you very much.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.