DAX Balance Sheet Include Closing Balance

DAX DAX Calculations
1

Hi folks, new to the forum hope you are all well.

I have a transaction report which is a general ledger transaction list down to account level from 1st Jan 2019 to 28th Feb 2020 I have managed to create a measure to calculate the cumulative vales for my balance sheet codes but how do i add the 31st December 2018 closing position to this so i have a proper balance sheet as at value?

My current measure which gives my cumulative values is as follows, but how do i add 2018 closing position to this?

3rd Party Receivables= 
CALCULATE([3rd Party Receivables (CCY)],
    FILTER(ALL(Dates),
    Dates[Date] <= MAX(Dates[Date])))

Thanks

2

Hi @BCS, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @BCS, have a look at CLOSINGBALANCE…

There is also OPENINGBALANCE too…

4

Hi @BCS

For your case, you will need to use “OPENINGBALANCEYEAR” function. This will give you the value on 31st December 2018 for dates in 2019.

If facing any issues with this function, share the PBIX and we will be able to help further.

Thanks
Ankit J

5

Hi @BCS, has this provided you a solution?.

6

Hi, thanks for the responses, much appreciated.

I will check out “OPENINGBALANCEYEAR” but for now I got my measure to work I’m not exactly sure how, by shear luck probably!

I had the closing balances in a separate table called “Values 2018 (LCY)” and retrieved the closing balance using the entity name and applied the date filter and it worked!!

I’m sure the Dax below looks nasty to most so can anyone suggest improvements to the below as I’m sure this measure is not as efficient as it could be!

3rd Party Receivables Entity1 (CCY) =

CALCULATE((CALCULATE(CALCULATE([Values (LCY)-Entity1],

FILTER(GL_Entity1,
GL_Entity1[G/L Account No.] = 2110 ||
GL_Entity1[G/L Account No.] = 2120 ||
GL_Entity1[G/L Account No.] = 2130 ||
GL_Entity1[G/L Account No.] = 2140 ||
GL_Entity1[G/L Account No.] = 2150 ||
GL_Entity1[G/L Account No.] = 2160),

FILTER(ALL(Dates),
Dates[Date] <= MAX(Dates[Date]))),
ALL(‘Cust _Post_Groups’[Customer Posting Groups]),

FILTER(ALL(Dates),
Dates[Date] <= MAX(Dates[Date])))+

CALCULATE([Values 2018 (LCY)],
FILTER(ALL(Dates),
Dates[Date] <= MAX(Dates[Date])),BS_2018_Close[Entity] = "Entity1")))*

LOOKUPVALUE(FX[Value],FX[Date],MAX(Dates[Date]),FX[Currency Code],
LOOKUPVALUE(Entities[Functional Currency],Entities[Entity Name],"Entity1"))  // convert to consolidated currency using month end rate
7

Hi @BCS

First, do check OPENINGBALANCEYEAR as based on your requirement, that should work. However if that is not successful, share your PBIX file to look into this.

Thanks
Ankit J

8

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!