Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
below is an image of my model
i have also created a redacted 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.
Proud to be a Super User!
Try and replace your variable with this:
VAR LastBalanceDate = ENDOFMONTH(MAX(RM Management_Accounts[Date]))
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]))).
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
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
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
Proud to be a Super User!
My misstake, endofmonth cannot use singular values.
Where are your transactions? GL Detail?
The individual transaction lines are in RM manament accounts
the GL details is a hieracy for the GL codes sub headers.
Proud to be a Super User!
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |