Forum Discussion
Running total until today
- 6 years ago
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.
jdbuchanan71is it possible to upload excel? I didn't succeed. therefore attached a screenshot.
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.