Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |