Dynamic reporting with Fiscal Year (Portfolio of Companies)

I am building a report in Power BI which deals with financial information. I have the same data points for c65 companies (Providers in my data set). My data tables are for P&L, Balance Sheet and Cash Flow. I also have a calendar table and a lookup table with information about each provider which includes fiscal year end month. All data points are quarterly only.

I am building my report to be dynamic based on selecting a certain Provider, and a Fiscal Year.

I am following the approach used in the Enterprise DNA financial reporting course and have set up templates for each financial statement to show the correct layout order. I have built measures to show the quarterly results.

My P&L data table has the following column headers:

The provider column lists the company names, Actual/Budget column is either “Actual” or “Budget”, P&L Line and P&L Statement list the line items, e.g. income, staff costs etc and are broadly the same. I have two columns as one comes from the report and the other links to the template where I have adjusted formatting slightly. Date is a calendar date and is always the end of a quarter (we only get quarterly data points). Fiscal Quarter contains either “Q1”,“Q2”,“Q3”,or “Q4”, and Value is the numerical amount for that data point. The other columns I have added in order to calcluate for each data point which fiscal year it relates to by comparing the month of each data point to the year end month (which varies for the different providers). FY column contains the fiscal year for each data point, e.g. “FY20”.

I have successfully displayed my P&L table similar to the example in the course, showing results for each quarter using measures. I have added measures to calculate margins and total interest, which is not in my data set.

I have the same measure for each quarter to display results in the table, e.g. for Q1:

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])
)

I have two tables which are the same, and I filtered one table for Actual results, and the other for Budget.

I am now trying to build a way to compare YTD actual figures to YTD budget. I always have complete data for the current fiscal year (all 4 quarters) but may not have all actual results yet dependent on when the fiscal year end is for that provider. So, if my provider only has actuals up to Q3, I wish to compare actual totals for Q1-Q3 to budget totals for Q1-Q3 only and ignore Q4 budget.

My approach was to build a measure to ascertain which is the latest actual quarter, by checking to see if the income in that quarter is greater than zero. I built similar measures for each quarter, for example:

Grp_Q3_Test =

sumx(FILTER(‘Group P&L’,‘Group P&L’[P&L Line]=“Group Income”&& ‘Group P&L’[Fiscal Quarter]=“Q3” && ‘Group P&L’[Actual/Budget]=“Actual”),‘Group P&L’[Value])

This returns the income amount for Q3 actuals. I have the same for all quarters.

I then built the following measure to calculate YTD:

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])
)

My logic is that this will assess whether Q4 income is greater than zero, then count results for all quarters if that is the case, and so on, until it only counts Q1. I have not at this point figured out how to deal with YTD margins.

This measure works perfectly when I add it to my table which is filtered to Actuals, however when I add to the table filtered to Budget it defaults to show only Q1 no matter when the last actual quarter of data was. I believe this is something to do with the table filter but not sure.

I would be happy to have this measure in a separate table if the table filter is interefering, but am not sure of the best approach.

I tried writing a Year to Date budget measure as above, but using an additional filter for the value where Actual/Budget = “Budget”, but I cannot add a third filter context && using this approach.

Any suggestions on how I could approach solving this would be wonderful.

Hi @Joanna25, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Hi @Joanna25,
Please use the below measure based on using your measures and date table. This will restrict anything in the last year which is greater than as of now.
YTD PY =
CALCULATE(
TOTALYTD( [Measure],
PARALLELPERIOD(‘Table’[ Date],-1,YEAR),
FILTER( ALL(‘Table’[ Date]),
‘Table’[ Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(MAX(‘Table’[ Date])))
)
)
)

For YTD, you can use this simple measure 
YTD =TOTALYTD([Measure],DATESYTD('Table'[Date]))

I hope this is what you wanted as per my understanding. If not then please let me more details or share a sample file to address the problem in more better way.

Thanks
Shubham

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up