Forum Discussion

Oomsen's avatar
Oomsen
Helper III
6 years ago
Solved

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 o...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    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.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

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's avatar
Oomsen
Helper III
6 years ago

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's avatar
    jdbuchanan71
    Super User
    6 years ago

    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's avatar
      Oomsen
      Helper III
      6 years ago

      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's avatar
        jdbuchanan71
        Super User
        6 years ago

        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.