Financial Balance calculation for Last 13 months

[L33]

Hi
I've just started building a Power BI data model that will mostly be used via the Analyze in Excel feature in pivot tables, and have hit a problem that feels like it should be simple, but I am struggling!

I'm returning the sum total balance of transactions at the end of each month, but need to be able to dynamically show these numbers for the last 13 months based on the selection of a single month.

My balance is determined by this DAX:

Balance (Accumulating Sum of Transaction Amounts) =
VAR MaxDate = MAX ( 'Calendar'[Calendar Post Month] ) -- Saves the last visible date
RETURN
    CALCULATE (
        [Sum Transactions Amount],            -- Computes sales amount
        'Calendar'[Calendar Post Month] <= MaxDate,   -- Where date is before the last visible date
        ALL ( 'Calendar' )               -- Removes any other filters from Date
    )

Followed by this:

Balance at end of Selected Month =
TOTALMTD(
    [Balance (Accumulating Sum of Transaction Amounts)],
    Calendar[Calendar_Date]
)

Basically - how do I write the DAX so that I can achieve the middle table where I've manually selected 13 months from the slicer above it, but using the slicer for third table where I'm selecting just one month?

 

[L33]

In one of those moments where you suddenly realise "Of course!", I did a bit more googling and found the answer, and - as usual with these things - it lays in the construction of the data model.

I've had to create a disconnected Calendar table (in fact, a cut down version which is only a list of the distinct Post Months from that table) and then the following formula allows me to pick the single month from that disconnected table to get me to that middle table I wanted:

Balance for Each of Last 12 Months =
VAR TRAILING_MTHS = 12

VAR PreviousDate =
    FIRSTDATE (
        DATEADD (
            'Post Months (Disconnected)'[Posting Month (dis)],
             TRAILING_MTHS * -1,
            MONTH
        )
    )

VAR Result =
    CALCULATE (
        [Balance at end of Selected Month],
        KEEPFILTERS ( Calendar ),
        Calendar[Calendar_Date] >= PreviousDate
            && Calendar[Calendar_Date] <= MAX ( 'Post Months (Disconnected)'[Posting Month (dis)] )
    )

RETURN
Result

The "Calendar Post Month" in the table still comes from the main Calendar table. The only drawback here is that my users will have to understand it a bit, remembering which tables to pick the relevant fields from.

If anyone knows a way to achieve the same ends without a disconnected table, I'd be very grateful!