Show running total against date table but only till the max period in transaction

[smpa01]

I am working with three tables and the schema is following (pbix is attached)

'Calendar' filters 'Transactions' through 'key'.

I am trying to show a running total from transactions by bringing X axis from calendar. But I want this running total to display till the max period I have in my transactions.

I am currently doing this

But I want this cause the max period in transaction is 202104

Off course, I can do this but it wipes out the total

What is an elegant way to achieve this? 

Thank you in advance

@AlexisOlson 

[smpa01]

@AlexisOlson  Thanks for this. I did not see this sort of table application before. Is there any blog post or anything from SQLBI explaining this?

I do have a follow up Q though

I pushed your solution little further to test one more idea to see if instead of passing a `Transactions` table, whether it is possible to pass on a FIltered `transactions` table.

I ingested one row in each Key and Transaction table for Year=2020 data and I wanted to see if there is a way to calculate on running total for Transactions[Year]=2021 and Transactions[period]<=202104.

The normal measure is this

runningTotal = 
VAR _mxP =
    CALCULATE(MAX ( 'Calendar'[period] ),Transactions)
VAR _runningT=
    CALCULATE (
        SUM ( Transactions[Sale] ),
         'Calendar'[period] <= _mxP 
    )
RETURN _runningT

I changed to this

runningTotal = 
VAR _mxP =
    CALCULATE(MAX ( 'Calendar'[period] ),FILTER(Transactions,Transactions[Year]=2021))
VAR _runningT=
    CALCULATE (
        SUM ( Transactions[Sale] ),
         'Calendar'[period] <= _mxP 
    )
RETURN _runningT

which did not give me what I wanted, so I tried like this

runningTotal = 
VAR _mxP =
    CALCULATE(MAX ( 'Calendar'[period] ),Transactions)
VAR _runningT=
    CALCULATE (
        SUM ( Transactions[Sale] ),
         'Calendar'[period] <= _mxP , Transactions[Year]=2021
    )
RETURN _runningT

Do you have any possible explanation?

[AlexisOlson]

This article talks about this sort of filtering a bit:
https://www.sqlbi.com/articles/filtering-tables/

I'm not quite sure what part you're requesting an explanation for. Adding a year = 2021 filter doesn't change what the maximal date is so it won't affect the result unless it's part of the filter context when calculating the sum.

[AlexisOlson · Accepted solution]

I'm not sure why there's an intermediate 'key' table but you can use it or 'Transaction' as a tablefilter for your max date.

runningTotal = 
VAR _maxDate = CALCULATE ( MAX ( 'Calendar'[Date] ), 'key' )
RETURN
    CALCULATE (
        SUM ( Transactions[Sale] ),
        'Calendar'[Date] <= _maxDate
    )