Multiple Running totals

[Anonymous]

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

[MFelix · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.