Running total until today

[Oomsen]

Hi, i'm looking for a measure or dax to calculate the expected cashflow of my receivables. 

I have a table with receivables based on due date. 

Some of the due dates allready past which means the outstanding amount must be added to today's outstanding balance.

All historical receivables must be in de balance of today and all future receivables should stay on there own due date. 

Invoice      due date          amount       

1                04-03-2019     1000           

2                05-06-2019     1000           

3                28-10-2019      1000          

4                30-10-2019      500              

I'm expecting a balance of 3000 today and 500 on 30-10. 

[jdbuchanan71]

Hello @Oomsen 

Give this a try:

Balance = 
VAR _LastDate = MAX ( receivables[Due Date] )
RETURN
IF ( _LastDate > TODAY(), SUM ( receivables[Amount] ),
CALCULATE( SUM(receivables[Amount]), ALL(receivables[Invoice]) , receivables[Due Date] <= _LastDate) )

[Oomsen]

@jdbuchanan71  this brings me in the right direction. Did you make a table or measure?

I only want to adjust that historical amounts are blank.

[jdbuchanan71]

This is a measure and if you want older dates blank it would be like this.

Balance = 
VAR _LastDate = MAX ( receivables[Due Date] )
RETURN
IF ( _LastDate < TODAY(), BLANK(),
IF ( _LastDate > TODAY(), SUM ( receivables[Amount] ),
CALCULATE( SUM(receivables[Amount]), ALL(receivables[Invoice]) , receivables[Due Date] <= _LastDate) ) )

[Oomsen]

@jdbuchanan71  it's almost working 🙂

I'm running the overview by week and see that the historical sum is not working correct. 

Below a screen shot. "Weeknummer" = Week, "amount" is receivables, deb2 is your measure. 

[jdbuchanan71]

@Oomsen 

Where are you getting the week number, do you have a date table you are using?  You would have to change the measure to reference the date table if so.

[Oomsen]

@jdbuchanan71 i have a date table. Where do you suggest the adjustment?

[jdbuchanan71]

Can you share your .pbix so I am not guessing at the structure?

[Oomsen]

@jdbuchanan71 because of confidentially sharing is difficult. Is there another way to share the necessary data with you?

[jdbuchanan71]

You can create a dummy data set in excel that matches your structure (table names, field names, relationships).  This makes it much easier to help solve the problem you are having.

Give this measure a try, it assumes the date table is called "Dates" and the date field is "Date"

Balance = 
VAR _LastDate = LASTDATE ( Dates[Date] )
VAR _FirstDate = FIRSTDATE ( Dates[Date] )
RETURN
    IF (
        TODAY () >= _FirstDate && TODAY () <= _LastDate,
        CALCULATE ( SUM ( receivables[Amount] ), Dates[Date] <= _LastDate ),
        IF ( _LastDate > TODAY (), SUM ( receivables[Amount] ) )
    )

[Oomsen]

@jdbuchanan71is it possible to upload excel? I didn't succeed. therefore attached a screenshot.

[jdbuchanan71 · Accepted solution]

You can create the file and load it to DropBox or OneDrive.  I need to understand what are all the tables involved and how they are joined so your sample .pbix should replicate the real structure.

You use the excel file to create the dummy data then load it to your sample .pbix, join the tables and share that file.

For example, my questions from your screen shot are

1. Does the week field come from the date table?

2. Does the date table join to the receivables table on the invoice date or due date field?

This measure gives me the result you are asking for assuming the date table is joined on the due date.

Invoice Amount = SUM ( receivables[amount] )

Balance2 = 
VAR _LastDate = LASTDATE ( Dates[Date] )
VAR _FirstDate = FIRSTDATE ( Dates[Date] )
RETURN
IF ( NOT ISBLANK ( [Invoice Amount] ),
    IF (
        TODAY () >= _FirstDate && TODAY () <= _LastDate,
        CALCULATE ( [Invoice Amount] , ALL ( receivables ) , Dates[Date] <= _LastDate ),
        IF ( _LastDate > TODAY (), [Invoice Amount] )
    )
)

If you replicate your model in a sample .pbix I would understand the structure and it would help me answer your question without going back and forth multiple times.