Using DAX in Power BI to solve Closing Balance for the Month with semi additive measures!

This video takes a look at many different approaches for solving the Closing Balance for the month calculation. We use Power BI and DAX in this example, specifically we take a look at the functions LASTDATE, LASTNONBLANK, CLOSINGBALANCEMONTH and PARALLELPERIOD.
If you enjoy this video and are interested in formal training on DAX, Power BI, Power Apps, Azure, or other Microsoft products you can use my code "Mitchell20" to receive a discount at check out when purchasing On-Demand Learning classes from
pragmaticworks... #PragmaticWorks #MitchellPearson #MitchellSQL
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - -
Next step on your journey:
👉 On-Demand Learning Courses FREE Trial: www.pragmaticw...
🔗Pragmatic Works On-Demand Learning Packages: pragmaticworks...
🔗Pragmatic Works Boot Camps: pragmaticworks...
🔗Pragmatic Works Hackathons: pragmaticworks...
🔗Pragmatic Works Virtual Mentoring: pragmaticworks...
🔗Pragmatic Works Enterprise Private Training: pragmaticworks...
🔗Pragmatic Works Blog: blog.pragmaticw...
Let's connect:
✔️Twitter: / pragmaticworks
✔️Facebook: / pragmaticworks
✔️Instagram: / pragmatic.works
✔️LinkedIn: / pragmaticworks
✔️TH-cam: / pragmaticworks
Pragmatic Works
7175 Hwy 17, Suite 2 Fleming Island, FL 32003
Phone: [phone]
Email: [e-mail]

[@GeorgeKlucsarits]

Brilliant example and very timely for me! Thanks. I especially appreciate you walking through the steps that most of us would probably try. It's always frustrating to find a solution that works, until it doesn't. Cheers!

[@PragmaticWorks]

Glad it was helpful!

[@jimgleason4040]

This was fantastic as usual! You always take on the sometimes difficult problems and are very thourough and clear the way you explain your approach.

[@totvabe1]

What about using CALCULATE( [Close Price], LASTNONBLANK('Date'[Date], [Close Price],), ALLEXCEPT('Date'[Day]) ) ??? Would it be possible?

[@Baldur1005]

It should be other way around. The All(‘Date’[Day]) would work. The ALLEXCEPT would give you numbers from last month for each day respectively which I guess in this scenario would be wrong

[@borstibo]

Hi Mitchell, great video! Makes all sense when learning more DAX and put it to use. I'm struggling with a data set: customer accounts with acc balance for each time a transaction was done... there may be 3 transactions in May (LASTNONBLANK works) but there may also be 0 transactions in June. Now the closing balance for June should be the same as after the last transaction in May. How can this be solved best using the DAX functions you presented?

[@DIGITAL_COOKING]

Good one Mitchell , now you can take your vacation:)

[@PragmaticWorks]

Thanks!

[@gregorytaylor2749]

Fantastic tutorial!! Thanks!!

[@PragmaticWorks]

You're very welcome!

[@abrilgonzalez7892]

Where could we download the dataset? Thanks you!

[@jihwankim2278]

Awesome!!

[@PragmaticWorks]

Thank Jihwan! Glad you liked it.

[@josericardo3015]

Tks for the excellent video!
By the way, what is the Dax Expression for the Close Price measure?

[@youaresociety4035]

What was the content in close price measure

[@lauracruz6819]

Hi, thanks for this. I have something quite similar but with mine it's a daily ending balance I'm after. Sometimes a day has multiple ending balances and sometimes there is no ending balance for the particular date if there was no transaction on the date. I've followed along closely to your video and don't understand why my formula errors. Can you help me?
End of Day Balance:=CALCULATE(
[Ending Balance],
LASTNONBLANK(
PARALLELPERIOD(
GLI[Date],
0,
GLI[Date])
,
[Ending Balance]))

[@lauracruz6819]

sorry I meant to include thar when the date has multiple ending balances it's the last entry I'd like to return. Thanks!