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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.