Hi All
I am adding to this thread with a related question (am new here, so hope this is ok?!)
I am dealing with financial data with a portfolio of companies (providers) and my P&L data table has these columns:
P&L Line and P&L Statement are essentially the same thing, just utilising better format/wording in my template based on P&L Statement column. Date is an actual date (linked to calendar table but not relevant to what I am doing). Fiscal Quarter column contains “Q1”, or “Q2”, or “Q3”, or “Q4”, and the remaining columns allow me to figure out what the fiscal year is - each company in the portfolio may have a different fiscal year end.
I have set up a template table to display results in P&L format using the approach taken in Enterprise DNA financial reporting course and have succesfully shown actual and budget data in two separate tables, broken down by Q1 - Q4 using measures. I have a page filter for provider and fiscal year (FY).
Currently I have two separate tables, one filtered for Actual and one for Budget which works fine. I am just not sure I will be able to create a column that shows YTD actual and budget, including the margin calculations, dependent on when the latest actual quarter data is.
My quarterly data is displayed in both tables using the following measure (including measures for margins which are calculated from my data set:
Q1 =
VAR CurrentItem = SELECTEDVALUE(‘Template_Group_PL’[P&L Statement])
Return
SWITCH( TRUE(),
CurrentItem = “EBITDARM Margin”, FORMAT([Grp_EBITDARM_Mgn_Q1],“0.0%”),
CurrentItem = “EBITDAR Margin”, FORMAT([Grp_EBITDAR_Mgn_Q1],“0.0%” ),
CurrentItem = “EBITDA (p/e) Margin”, FORMAT([Grp_EBITDA_PE_Mgn_Q1],“0.0%”),
CurrentItem = “Group EBITDA Margin”, FORMAT([Grp_EBITDA_Mgn_Q1], “0.0%” ),
CurrentItem = “Total Interest”, [Total Interest_Q1],
SUMX(FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”),‘Group P&L’[Value])
)
This is repeated in a measure for each quarter.
Now I am trying to calculate a YTD figure for both the actual and budget values that will depend on when the last actual quarter of data is, so that I am comparing apples with apples (budget will always have full year of data).
My approach was to build measures to check if the actual income in that quarter was greater than zero, example:
Grp_Q4_Test =
sumx(FILTER(‘Group P&L’,‘Group P&L’[P&L Line]=“Group Income”&& ‘Group P&L’[Fiscal Quarter]=“Q4” && ‘Group P&L’[Actual/Budget]=“Actual”),‘Group P&L’[Value])
I have one of these for each quarter.
I then built a YTD measure like this:
Year to Date =
VAR CurrentItem = SELECTEDVALUE(‘Template_Group_PL’[P&L Statement])
Return
SWITCH( TRUE(),
[Grp_Q4_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”||“Q3”||“Q4”),‘Group P&L’[Value]),
[Grp_Q3_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”||“Q3”),‘Group P&L’[Value]),
[Grp_Q2_Test]>0, SUMX (FILTER(‘Group P&L’,‘Group P&L’[P&L Statement] = CurrentItem && ‘Group P&L’[Fiscal Quarter]=“Q1”||“Q2”),‘Group P&L’[Value]),
SUMX (FILTER('Group P&L','Group P&L'[P&L Statement] = CurrentItem && 'Group P&L'[Fiscal Quarter]="Q1"),'Group P&L'[Value])
)
I don’t know if this is correct, or just fluke, but it works on my table with the actual data in it (table has filter for “Actual” results only. It does not yet deal with the margin measures…
My issue is trying to get it to work for the budget calculation. I cannot add an additional && to filter for “budget” only in the Actual/Budget column and I am stuck for ideas on how to resolve. When I add the measure that works in the table filtered to actual, into the table filtered to budget, it defaults to only show Q1 budget data as I guess the table filter is interfering?
Essentially, if only Q1-Q3 have actual income greater than zero, I want this measure to count all the results for budget Q1-Q3 as well and not include Q4.
Is this possible? I would be happy to show this in a separate table within the report, if I could get it to calculate correctly! I would also like to include the margin calculation measures in this data if at all possible.
Any help much appreciated!
Thanks.
but if your initial question has been answered please mark the thread as solved. You can always create a new thread if further assistance is required down the line.