Projecting Year by Year

[itchyeyeballs]

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:

1. Dynamically Identify the most recent year for each group as a starting point to project from
2. Identify the % diffenence from the most recent year to year prior to that
3. 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?

[Phil_Seamark]

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. 

[itchyeyeballs]

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!

[SqlJason]

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.

[itchyeyeballs]

Thanks @SqlJason

I do have a date dimension and had set up the future years.

When creating the projection measure which table would it sit in the fact or the dimension table? 

Its further complicated because the projection measure needs to be a rolling total i.e.

1617 = latest actual * ratio

1718 = 16/17 projection * ratio

1819 = 1718 projection * ratio

[SqlJason]

The measure can sit anywhere, it doesn't matter. What matters more is whether the attribute from the fact or dimension is used in the calculation. I am still not 100% clear on the requirements, but based on what I understood, you will need to follwo something like this:-

1) Calculate max year from Fact table for hroup

MaxYear=calculate(max(Fact[Year]), ALLEXCEPT(Fact, Fact[Group]))

2) Get Sales this year

Sales TY = calculate(sum(sales), filter(All(date), Date[Year]=[MaxYear]))

and Sales Last year also

Sales LY = calculate(sum(sales), filter(All(date), Date[Year]=[MaxYear]-1))

3) For every group, find difference of sales from this maxYear and Year-1

SalesDiff = sumx(values(Group[Group]), [Sales TY] - [Sales LY])

4) Now you can apply your projection formula.

My syntax may not be 100% correct as I am just writing without testing. But if you give me some sample data, along with the end result, I can try it out at my side and give the correct formula also

[itchyeyeballs]

Hi @SqlJason

thank you, that looks like exactly what I need,

I've run into a problem straight away as the year field in the fact table is a string not a date so I'm having to rethink my model a bit (the actual model contains several fact tables and many dimensions so not a quick fix) .

Is it possible to rework your formula for MaxYear to instead reference an integer column in the dimension table?  Ive tried but keep getting the very last year rather than last year per group

I would upload some sample data but not sure of the best method.

[SqlJason]

Sure, I think you can try something like this for the MaxYear

=calculate(max(Date[YearIntColumn]), ALLEXCEPT(Fact, Fact[Group]))

The calculate will propagate the relationship in the opposite direction.

[itchyeyeballs]

Thats what I tried but it keeps on spitting out the same value for all the results (the maximum number in the dimension table).

Edit, resolved that issue by adding a numeric year column to the fact table

[SqlJason]

You can create a new calculated column iin your fact table, which brings in the integer field from the Date table to the fact.

=RELATED(Date[YearIntColumn])

[SqlJason]

Or you can use this formula

MaxYear:=CALCULATE(MAX('Date'[YearInt]), CALCULATETABLE('Fact',ALL('Date'[Year])))

I just tested this formula in mine, and it works from what I understood of your requirements.