cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srd16 Frequent Visitor
Frequent Visitor

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.

 

001.pngMatix002.pngModel

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Multiple Running totals

Hi @srd16 ,

 

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:

 

savings.png

 

Check also PBIX file attach.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Super User
Super User

Re: Multiple Running totals

Hi @srd16 ,

 

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:

last_savings.png

 

Also attached revised PBIX file.

 

Sorry for the misleading in the previous answer.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Multiple Running totals

Hi @srd16 ,

 

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:

 

savings.png

 

Check also PBIX file attach.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




srd16 Frequent Visitor
Frequent Visitor

Re: Multiple Running totals

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.  

Super User
Super User

Re: Multiple Running totals

Hi,

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

Highlighted
Super User
Super User

Re: Multiple Running totals

Hi @srd16 ,

 

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:

last_savings.png

 

Also attached revised PBIX file.

 

Sorry for the misleading in the previous answer.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 355 members 3,441 guests
Please welcome our newest community members: