How can we show a row with the initial balance in a Power BI report? Another video that answers a student's question! Download sample file: www.sqlbi.com/... Read more about the "unplugged" format: www.sqlbi.com/... #unplugged
This is a great Solution... I have little different requirement than this, where I have a GL Transaction Table and I am doing data Selection basis Posting Date. What is required is a New measure in GL Table where I can have Opening Balance which should be sum of all amount prior to start posting date selection. If I check opening balance at document number level then opening balance will be zero but if I do want to check at Account Level or higher up in the heirarchy then Opening balance will come.. The Problem is should happen post the selection done by the user through slicers and based on the selection opening balance is to be calculated
Fantastic Tutorial. I had some problems while practising such as My date value is not zero but sometime in 1899, the Opening Balance row disapeared, the slicer woudn't get changed ect... but in the end, everything is ok. It's so satisfied. Thanks a lot.
This is very interesting but how can we make it date wise instead yearly? I have to add the same opening balance in my account statement where i am calculating running balance for different transactions happens on different dates. and i have a date slicer for date selection.
Thx for a super interesting video, How would we get a opening balance for every period shown in the table. The result that I am looking for is a rolling balance. Basically to add the balance for a month to the next month's sum. Kind Regards Robin
Thank you very much for this great and very instructive video. I'm trying to calculate the opening balance account at a starting date but it always returns blank because the underlying table is obviously filtered out from that starting date. I don't know see in your video how did you solve that... Is it possible to have the PBIX file? Thanks.
A very interesting way to wrangle the output to a specific user requests. A good thought experiment, but I would certainly try to convince my users to implement this a different way to avoid DAX-complexities. :) One potential issue I see is how the grand total is detected. VAR IsGrandTotal = COUNTROWS( 'Date' ) = CALCULATE( COUNTROWS ( 'Date' ), ALLSELECTED( ) ) This would only work if there is more then 1 item on the rows. For example, if only the month Januari-2023 was selected / existed in the date table it would display the closing balance instead of the sales.
Good point. If you have a small number of columns visible, consider using ISFILTERED and check whether the columns that go in more details are (not) selected.
For the grand total it might be easier to add one more row with ending balance with the value 999999999 and then remove subtotals. On vacation, but I'll report back with results next week.
Hi SQLBI I have a task were I have 4 financial years, it starts July and end June. Now I want to be able to compare any financial year sales against each other. Now let's saying I'm comparing the current 2023 FY with 2020 FY to date, my current FY has data from July until may 3, I want to be able to compare 2023 FY until 3 may, same thing should happen to FY 2020,it should pick date until 3 may 2020. How do I do that?
I struggle with the complexity of this. While it is not in its own row, does this also provide the same result although in a less complex form? Initial Balance = VAR SelectedDate = MAX('Table1'[Date]) RETURN CALCULATE( SUM('Table1'[Trans_Value]), FILTER( ALL('Table1'), 'Table1'[Date] < SelectedDate ) )
