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.

Oomsen's avatar
Oomsen
Helper III
6 years ago

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

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

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] ) )
    )
  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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.

  • Oomsen's avatar
    Oomsen
    Helper III
    6 years ago

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