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.
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.
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
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.
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.
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 ) )
Thanks.. you don't give solution of one problem, you give concept how to solve problem. you are Legend
Amazing solution; we enjoy Dax when Alberto and Marco teach us in Sqlbi; thank you so much
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
always is a great pleasure to learn from you both, Alberto and Marco. a Master piece!
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.
Very interesting and insightful, as always.
Nice solution! Always enjoying your unplugged video!
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.
This video inspired to solve a problem in a completely different scenario! Thanks a lot! :-)
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.
You can download the sample file by using the link in the Description.
@@SQLBI Thank you very much!
Really great prsentation even for brginners. Is there any other presentation on how to add also a column with the running Total?
See www.sqlbi.com/articles/computing-running-totals-in-dax/
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
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.
Great walkthrough. Very helpful. Thank you!
Great! Thank you 👍. Similar to what we do to show top n and others row.
Interesting task to solve and great hands on solution. Thanks!
Brillant Alberto ! Thanks för those greay unpluged video ;)
great and effective video as usual
I think it's helpful in some cases of accounting domain! Am I right?
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.
Very very interesting 💫
how would I apply this concept to the previous day?
Absolutely brilliant
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?
Check these patterns: www.daxpatterns.com/time-patterns/
How can I make it with fixed balance refers to previous years ?
Great video! Keep it up!
Beautiful!
nice..but is it possible to make the "Opening balance" word and values in BOLD?
You can use special unicode characters, just try copy & paste from sites like that: yaytext.com/bold-italic
Thanks for your video.
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
)
)
The challenge is just to create a specific row for that value instead of a measure that appears in separate columns.
Wonderful.
Awesome!
Muito bom! Great😎