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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AnthonyTilley
Solution Sage
Solution Sage

Balance sheet balance at end of period

Hi all,

 

This one may be a little bit complicated so i will try to include as much info as i can.

all of the data in my files has been redacted so some figures may not add up perfectly.

 

I have a report in desktop that creates a single matrix for a Profit and loss, blaance sheet and cash flow.

the problem i am having is in the final stage of my calculations 

 

i have a measure that calculates the income statment amount at the final point in the given time periosd 

 

Final AMT =
Var ma = DimHeaders[Header MA Type]
VAR LastBalanceDate = MAX ( Dates[LastDayOfMonth] )
return if(ma = 1,CALCULATE ([Income Statement AMT], Dates[LastDayOfMonth] = LastBalanceDate),[Income Statement AMT])
 
so for example if the matrix is grouped by year it will show you the final balance of december for each year. if it is grouped by qaurter then you get the balances for the last month in the quater and for month then you will get the final balance for each month. my issue arrises when a Company cost center (CCN in my report) has no balanaces past a set point.
 
the martix should show the yearly balances for each header in the example below ccn 1 is correct shoes all relevent headers correctly.
 
ccn 1.png
 
 
in the image below the table is filtered to show only CCN 9, CCN 9 has not balances passed April because there is no forecast for this CCN. however my formula is cacluating the blaance for december not the final month and this is showing as zero this means the year total is showing as zero. and there for no balance sheet appears. (all headers after net profit)
 
ccn 9.png
 

below is an image of my model 

fs model.png

i have also created a redacted pbix file 

PBIX File

 

my aim is to get the balances to always show the final month in a time period, if the ccn has no values after april then show april balance as the final balance for the year when grouping by year.

 

 





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

Proud to be a Super User!




7 REPLIES 7
tex628
Community Champion
Community Champion

Try and replace your variable with this:

VAR LastBalanceDate = ENDOFMONTH(MAX(RM Management_Accounts[Date]))





Connect on LinkedIn

unfortunatly this does not work 

 

The syntax for 'Management_Accounts' is incorrect. (DAX(Var ma = DimHeaders[Header MA Type]VAR LastBalanceDate = ENDOFMONTH(MAX(RM Management_Accounts[Date]))return if(ma = 1,CALCULATE ([Income Statement AMT], Dates[LastDayOfMonth] = LastBalanceDate),[Income Statement AMT]))).

 

 





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

Proud to be a Super User!




You will need to elaborate a little bit more, 

I'm just going to guess that your date column in your facts table isnt actually named "RM Management_Accounts[Date]", replace it with the correct column and see if it works. 

If it doesn't work, could you please provide an image of the error? 

Br,
Johannes


Connect on LinkedIn

There is a date colunm in my table, there is also a report date that is already set to the eom of the date 

these are then joined to a date table on the report date.

 

below is an image of the error

 

date.png

 

using max of report date does not give an error but still gives the same effect that i described above 

the headers for CCN still dont go passed the Net profit 





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

Proud to be a Super User!




My misstake, endofmonth cannot use singular values. 

Where are your transactions? GL Detail?


Connect on LinkedIn

The individual transaction lines are in RM manament accounts

the GL details is a hieracy for the GL codes sub headers.

 





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

Proud to be a Super User!




Anonymous
Not applicable

Did you ever solve this?  I am having a similar issue in that I want to have one measure for displaying income statement and balance sheet accounts with balance sheet accounts obviously not summing.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.