Forum Discussion
Running total until today
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.
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.
11 Replies
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) )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.
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) ) )
