Forum Discussion
Projecting Year by Year
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.
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
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
