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