Forum Discussion

MBILGIC's avatar
MBILGIC
New Member
2 years ago

Calculating Monthly Balance Sheet - Help please

Hi Community,

 

I am trying to create a Balance sheet dashboard with some measures / kpis etc. my data source is Microsoft navision 2016 and i have a live connection to the database.

 

the two tables i am currently using and have a relationship is GL_Account (generic chart of accounts) and GL_Entry. 

within GL entry there is fields such as amount, posting date, cost centre, department codes, gl account etc. the unique identifier for the relationship is the GL account number. 

 

i want to display my balance sheet yearly in comparison the prior 2-3 years. so 2023, 22, and 21.

but i also want to have a date hierarchy where i display balance sheets monthly. the issue i am having is, when i put posting date and amount in a matrix by month, it calculates a net change/movement rather than "balance". 

can someone help with this please?

 

Thanks

Mustafa

 

 

 

 

Need Help

2 Replies

    • MBILGIC's avatar
      MBILGIC
      New Member
      2 years ago

      Not quite but i figured it out. i was able to obtain by using:
      CumulativeTotal = CALCULATE(SUM('GL Entry 23'[2023 Amount]),FILTER(ALL('GL Entry 23'),'GL Entry 23'[Posting Date]<=MAX('GL Entry 23'[Posting Date])))