Insert Begin Balance entry in matrix

[Dellis81]

Hello

I have an inventory based matrix, with a slicer by year.   I am looking for a way to insert the begin balance entry as a part of the running total display.   I have played with the 'date" table, by adjusting year by previous day, but that wacks up the current year's 12/31 cumulative total.

The line in green is what I'm hoping to get to when using a slicer by year

Are there any thoughts to get me started in the right direction?

Thank you in advance for the awesome support this forum provides. 

DAryl

[v-yiruan-msft · Accepted solution]

Hi @Dellis81 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Copy the following applied codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31DcyMDRX0lEKKC1KzkgsTgUyDQ0MlGJ1opXM9A1NYdIumcUF+cWJOUCmrhFU3tBI39gQmwJTqAJzfbB+C1TjYdotIJZb4NBsiZBG0mwCko0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Description = _t, #"Transaction Dollars" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Description", type text}, {"Transaction Dollars", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year"}, {{"Details", each Table.InsertRows(_,Table.RowCount(_), {[Year=[Year]{0},Date=#date([Year]{0},1,1),Description="BegBalance",Transaction Dollars=null]})}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"Date", "Description", "Transaction Dollars"}, {"Date", "Description", "Transaction Dollars"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Details",{{"Date", type date}, {"Description", type text}, {"Transaction Dollars", Int64.Type}, {"Year", Int64.Type}})
in
    #"Changed Type1"

2. Create a measure as below to get the cumulative values

Cumulative = 
1500
    + CALCULATE (
        SUM ( 'Table'[Transaction Dollars] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )

Best Regards

[Dellis81]

Oh awesome.   So basically a new line item was inserted for each year (Jan 1st)- with a simple description of BegBalance - with nulls as transactions Amount?.  I will have to work on the measure to calcuate the begin balance (right now you plugged in as $1,500.   Think I can make that happen.
Thank you - I will work on next few days (as I can) - and get back to you if issues.  But, will accept this as a solution - as it what I was seeking!   You got me on a different trail of thinking

Thank you again!