Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiple Running totals

Hello,

I am new to PowerBI, and would greatly appreciate some help. I am attempting to populate a matrix with values of three accounts (Savings, Checking, and Certificate) I am having trouble when one of the accounts has a value for a date, but another account does not. I am looking for there to be no blanks in my table. The most recent value should be assumed for any date that has no data for that day (ie Checking June 14 2018 should equle 1535.94). I have tried using the LastNonBlank to no avail.

 

MatixModel

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    For starting don't use the bidirectionallity of filter then create the following measures:

    Savings sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings) );
    CALCULATE (
        MAX ( Savings[Savings]);
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

Certicate sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate) );
    CALCULATE (
        MAX ( Certificate[Certificate] );
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
) 

Checking sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) );
    CALCULATE (
        MAX ( Checking[Checking] );
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

    Final result is below:

     

     

    Check also PBIX file attach.

     

    Regards,

    MFelix

  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    You are correct, because I used the MAX function on the values, need to pickup the maximum date and not maximum value.

     

    Redo your measure to the following:

    Savings sum = VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Savings[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings ) );
        CALCULATE (
            SUM ( Savings[Savings] );
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )
    Certicate sum = 
VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Certificate[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate ) );
        CALCULATE (
            SUM ( Certificate[Certificate] );
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )
    Checking sum = 
VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Checking[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) );
        CALCULATE (
            SUM ( Checking[Checking]);
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )

    See result below:

     

    Also attached revised PBIX file.

     

    Sorry for the misleading in the previous answer.

     

    Regards,

    MFelix

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous ,

     

    For starting don't use the bidirectionallity of filter then create the following measures:

    Savings sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings) );
    CALCULATE (
        MAX ( Savings[Savings]);
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

Certicate sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate) );
    CALCULATE (
        MAX ( Certificate[Certificate] );
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
) 

Checking sum = 
IF (
    MIN ( 'Date'[Date] )
        <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) );
    CALCULATE (
        MAX ( Checking[Checking] );
        FILTER ( ALLSELECTED ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
)

    Final result is below:

     

     

    Check also PBIX file attach.

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you, This is working, but only when the values in the accounts are going up.  If there is a drop in one of the accounts, it retains the largerst value.  

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        You are correct, because I used the MAX function on the values, need to pickup the maximum date and not maximum value.

         

        Redo your measure to the following:

        Savings sum = VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Savings[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Savings[Date] ); ALLSELECTED ( Savings ) );
        CALCULATE (
            SUM ( Savings[Savings] );
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )
        Certicate sum = 
VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Certificate[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Certificate[Date] ); ALLSELECTED ( Certificate ) );
        CALCULATE (
            SUM ( Certificate[Certificate] );
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )
        Checking sum = 
VAR Date_Selection =
    CALCULATE (
        LASTDATE ( Checking[Date] );
        FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Checking[Date] ); ALLSELECTED ( Checking) );
        CALCULATE (
            SUM ( Checking[Checking]);
            FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] = Date_Selection )
        )
    )

        See result below:

         

        Also attached revised PBIX file.

         

        Sorry for the misleading in the previous answer.

         

        Regards,

        MFelix