Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
10 years ago

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 gr...
Need Help
Tutorial Requests
SqlJason's avatar
SqlJason
Memorable Member
10 years ago

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's avatar
itchyeyeballs
Impactful Individual
10 years ago

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's avatar
    SqlJason
    Memorable Member
    10 years ago

    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's avatar
    SqlJason
    Memorable Member
    10 years ago

    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.