Measure for line chart that shows accounts receivable balance over time

[11Willem]

Hi all,

This is my first post on this community, nice to meet you! Here goes! Currently, I'm having a problem which I'm not able to solve and I'm looking for someone to push me in the right direction. I found similar topics, but none with the exact same problem. To keep things clear, I'm posting a simple example.

Table 1 (= plain dates)

Date

1 January 2020

2 January 2020

...

30 December 2020

31 December 2020

Table 2 (= accounts receivable)

Invoice number	Invoice date	Amount	Due date	Date paid
A1234	1 April 2020	EUR 1.000,00	14 April 2020	16 April 2020

This table has currently only one entry, but in reality it will have more off course.

Desired result

I would like to generate a line chart that shows the accounts receivable balance for the full 2020 (daily). So on the X-axis I would like to have the dates from Table 1. On the Y-axis, I would like to have the balance from Table 2. In this example the graph would look like this:

1 Jan - 31 Mar: EUR 0,00

1 Apr - 15 Apr: EUR 1.000,00 (bump up)

16 Apr - 31 Dec: EUR 0,00 (bump down, back to zero)

Thought/actions

Well, what did I do so far? I'm not an expert, but I know calculated columns will not help me out here since I need something "dynamic". I want Power BI to evaluate for each date if this invoice was receivable at that date. I tried to create a measure using a combination of SUMX and FILTER, but to my knowledge FILTER is quite static (only one fixed date possible).

Furthermore I'm unsure whether I should create a relationship between Table 1 and Table 2 in the data model. And if yes, which date (Invoice, Due, Paid) from Table 2 should be connected to Table 1.

I'm even starting to doubt if a measure can help me out here.

So in the end, I'm not getting my head around this problem.

What are your thoughts about this? Many thanks in advance for your help!

Kind regards,

Wim

[Anonymous]

DSO is used to calculate how quickly you are able to collect the money owed to you after the sale has been completed; the average collection period. DSO= (Accounts receivable / total credit sales) x number of days in period. Interpreting this metric correctly is critical.

[camargos88 · Accepted solution]

Hello @11Willem ,

Check this file: Download PBIX

If you consider it as a solution, mark it as a solution and congratulations.

Ricardo

[11Willem]

Hi Ricardo,

My response is a bit late, but thanks for your reply. This is what I was looking for: a clear simple example that shows how you can approach this problem. I replaced your nested IF with a SWITCH, but that's just a personal preference. I assume as long as there's not much nesting, the performance would be about equal. Thanks again.

Kind regards,

Wim