Hi all,
I am back looking for some more help.
I am building a BI model that will load in 100+ companies financial statements.
As in my last post I am loading up the data in the format of the attached file. In this file I categorise items as annual data or interim data (in this example quarterly).
A key items is to be able to show the trailing 12 months figures aside of the annual figures.
I wrote this formula which I thought was great - it allowed for annual data when the interim is 0 (I only start interim data from 2015 but have annual until 2010)
This works perfectly in the single airline situation (note the last date in my date table is dynamic using the list.max query which is linked to the the financial data table).
But what struck me is if I load in 100 airlines, the max date in the date table will be the latest date of the most recent airline that reports. Fore example if Delta Air Lines has reported financial data for 31 Dec 2021 while Aegean Airlines has only reported up to 30 Sept 2021, the date table max date will be 31 Dec 2021 aligned to the max date in the financial data.
This creates a second round impact, because if I am looking at Aegean airlines the formula I wrote looks for the max date (31 Dec 2021 - due to Delta figures) and sums up the last 4 quarters. In the case of Aegean (the airline I am trying to filter) it would sum up all of 2021 but Q4 will be blank and it will give me only the 3 quarter of 2021 that have data for Aegean and not the last 12 months or last 4 quarters that I actually have data for this airline.
In summary I need to find a way to say max date is the most recent date that has data associated with the carrier that I am selecting. But this is where I am struggling to figure out what my maxdate Var should be so that it will only ever take the last date that there is data for Aegean and not just the last date in the total date table.
As ever all and any insight or help would be greatly appreciated.
Please note that in the BI model attached the last date on the date table is manually put in as 1/1/2023 as I was testing the concept I outlined above.
Airline Financial and Operational BI Model.pbix (1.3 MB)
Airline Financial and Operational Datasets.xlsx (136.2 KB)
Airline Master Sheet.xlsx (28.5 KB)
One of the items I wanted
*** READ FIRST ***
Before you send your question.
Make sure that all details relevant to your question is complete:
- Your current work-in-progress PBIX file - VERY IMPORTANT
- A clear explanation of the problem you are experiencing
- A mockup of the results you want to achieve
- Your underlying data file
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Also make sure that your data file contains no confidential information. If it does, click the link above.
*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***
