Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
junaidch278
New Member

Opening balance from running total column

Hi there.

Thanks in advance. I am having a problem while calculating opening and closing balance on the PBI from the following table.

junaidch278_0-1686319876371.png

I have tried these 2 dax calculations but it is not reflecting the desired results.
CALCULATE
(
min(FactStockMovement[ClosingBalance]),
FILTER(
ALLSELECTED(DimDate[Date]),(DimDate[date])=min(DimDate[date])
))
CALCULATE(
min(FactStockMovement[ClosingBalance]),
FILTER(
ALLSELECTED(DimDate[Date]),(DimDate[date])=FIRSTDATE(DimDate[date])
))
Can anyone guide me how can i calculate opening and closing balance from the table.
the 1st expression is working for the values having opening and closing balance, but if there is no transaction on the min and max date we are getting null.
looking forward to hearing from you guys.
3 REPLIES 3
some_bih
Super User
Super User

Hi @junaidch278 to provide clear solution in some way, the amounts as input and output should be clear.

In general, are these inputs for amount like stock, etc cash balance or like income, etc salary.

What about wanted output?  From picture stock is wanted to be shown, like opening balance +/- inputs / outputs = ending balance, right?

Give us some example data as input and expected output per X (x could be date, product key...)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






junaidch278
New Member

HI @some_bih 
I have the requirement where user can select any date. it can be a single day or a week or a couple of months. and the solution you perposed is workable for month, quarter and year opening and closing balance.
i'm looking for a way to select dynamic minmum date so i can utilize the running totals to get the correct results.
please reach out to me if you need aditional clarity.

some_bih
Super User
Super User

Hi @junaidch278 you should probably use functions OPENING/CLOSINGBALANCE* for DAX. check link

https://www.sqlbi.com/tv/closingbalancemonth-closingbalancequarter-closingbalanceyear-dax-guide/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.