Forum Discussion
Projecting Year by Year
Hi all,
I'm looking to setup an analysis that projects current data forward for the next 10 years. My model has a fact table which is linked to a date dimension table. The date dimension has a granularity of 1 year per row (we only collect data at an annual level). The year item in the fact table is a text field not a date.
I currently have a measure which calculates a total by year for each group in the fact table
I need to achieve three things:
- Dynamically Identify the most recent year for each group as a starting point to project from
- Identify the % diffenence from the most recent year to year prior to that
- Generate a value for the each of the next 10 years for each group based on a simple formula using the % diff identified in step 2.
At this stage I'm not interested in using any inbuilt forecasting or projection methods in PBI or R. I just need to apply the simple formula to roll forwards for the next 10 years.
Any ideas how I can go about this?
10 Replies
If your data comes from a table in a database such as MS-SQL it's pretty easy to shape the data to suit your needs in there first.
Hi
The underlying issue I have is I need power BI to calculate the totals on the fly based on the selected criteria then dynamically create projections for each of the next 10 years.
My fact table looks like
Year - Dept - sub dept - value
1516 - a - 1 - 100
1516 - a - 2 - 150
1516 - b - 1 - 50
1516 - b - 2 - 75
1516 - b - 3 - 25
I have a measure which creates a total for each department called Sum_FTE which I use in a matrix to create
Dept - 1314 - 1415 - 1516
a - 200 - 250 - 300
b - 100 - 350 - 500
c - 100 - 125 -
The challenge, how can I then create data for the years that haven't happend yet based on the calculations above?
Dept - 1617 - 1718 - 1819
a - ? - ? - ?
b - ? - ? - ?
c - ? - ? - ?
This needs to be dynamic to if I filter by another critera or add a another sub department etc the figures adjust themselves. I have added the required future years to my date dimension but how do I create the actual values?
I have thought about adding dummy data to my fact table using a left join in the sql db but I have a feeling that could get messy!
You need to revise your data model (if it is not in this form already). Apart from your fact table, you also need a Date table which will have all the list of Years that you need (for eg, you might have data in fact only till 1516, but your date table should contain years till you need it, maybe till 1920). Now make a relationship from the fact to the date table.
Once you have done that, you can have a measure for actual, a measure for projection and maybe use another measure that combines both into one measure, something like,
Result = if([Actual]>0, [Actual], [Projection])
Such a measure will show actuals if actuals are present else show the projection.
This way you don't need to do a left join, and this would be the right way to model also. Hope you got what I said.
