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