cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dellis81
Responsive Resident
Responsive Resident

Insert Begin Balance entry in matrix

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

 

BegBalanceEntry.PNG

 

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

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

 

DAryl

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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"

yingyinr_0-1636366136538.png

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] )
        )
    )

yingyinr_1-1636366688694.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
yingyinr
Community Support
Community Support

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"

yingyinr_0-1636366136538.png

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] )
        )
    )

yingyinr_1-1636366688694.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dellis81
Responsive Resident
Responsive Resident

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!


Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors