Accounts receivable montly

[Morten_DK]

I have a table for accounts receivables (payables by customers) that contains:

"Posting date"      "Closed at date"      "Due date"      "Customer number"       "Amount"

2021-09-01            2021-12-15            2021-12-01         ABC1                           100

In the above example I would like to calculate the montly receivables so:

September = 100

October = 100

November = 100

end of December = 0

So I need to calculate a measure that states that if the date from the date table is higher than or qual to the posting date from receivables table and also closing date is higher than date from data table then show receivables amount. 

I have tried with the following measure:

Outstanding Invoices =

var StartDate = calculate(Max(Receivables[Posting Date]),all('Date'))

var ClosedDate = calculate(Max(Receivables[Closed at Date]),all('Date'))

RETURN

CALCULATE (

Receivables[Invoiced Receivables],

'Date'[Date] >= StartDate && 'Date'[Date] <= ClosedDate

)

That returns the correct receivables amount but on all months is the same. There is no relationship between receivables table and date table

[v-zhangti]

Hi, @Morten_DK 

You can take a look at the following methods, hoping to solve your problem.

Create a new date table.

Date = CALENDAR(DATE(2021,8,1),DATE(2021,12,31))

Measure =
IF (
    MAX ( 'Date'[Date] ) >= MAX ( 'Table'[Posting date] )
        && MAX ( 'Date'[Date] ) < MAX ( 'Table'[Closed at date] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            [Date].[Month] <= MAX ( 'Table'[Closed at date].[Month] )
        )
    ),
    BLANK ()
)

Max ('date '[date]) is the date that can be selected on the right side of the date slicer.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Morten_DK]

Thanks!

That seems to be working on individual order numbers, but if I take an entire month without filtering on particular orders, then the sum for the month is blank. If I drill down to order numbers then I get the correct value per order, but not on month level, that is still blank.

Is there a way to force the calculation on row level and then sum up, if that is the cause of the problem in the first place? 

[v-zhangti · Accepted solution]

Hi, @Morten_DK 

You can add a condition to filter Customer number in the measure.

Measure = 
IF (
    MAX ( 'Date'[Date] ) >= MAX ( 'Table'[Posting date] )
        && MAX ( 'Date'[Date] ) < MAX ( 'Table'[Closed at date] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            [Date].[Month] <= MAX ( 'Table'[Closed at date].[Month] )
        ),FILTER(ALL('Table'),[Customer number]=MAX('Table'[Customer number]))
    ),
    BLANK ()
)

If the method I provided above can't solve your problem, what's your expected result? Can you provide an example diagram of your desired outcome?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Morten_DK is it assumption based rule or there is data to back up this logic?

[Morten_DK]

Thats a logic.

When an invoice is posted (posting date) it stays in the accounts receivable until it is paid (closed at date).

The following works apart from not stopping at Closed at date:

Outstanding Invoices =
CALCULATE (
Receivables[Invoiced Receivables],
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

if I modify it to:

Outstanding Invoices =
CALCULATE (
Receivables[Invoiced Receivables],
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) &&'Date'[Date] <= max(Receivables[Closed_at_date])  )
)

Then I don't get all the months in between. 

[parry2k]

@Morten_DK what made December value to 0, what is the logic?

[Morten_DK]

@parry2k that is because at end of december (31/12-2021) we are past "Closed at date" which means the outstanding amount has been paid, thus no outstanding anymore.