Forum Discussion

ChrisWilliams's avatar
ChrisWilliams
Advocate II
9 years ago
Solved

Fiscal Year Calculated Columns from date ranges

I'm creating a date table and I've used the Calendar() function as the basis.  So, I have a Date column in the Dates table, as well as other calculated columns for Month, Quarter, etc.  I want to add an additional calculated column for Fiscal Year, based on start and end dates defined in another table in my model.

 

I have a GLYEAR table that has the following structure:

Date Start_Date

Date End_Date

int  FRL_FISCAL_YEAR

 

Basically the psuedo code for the calculated colum should be "If Dates[Date] >= GLYEAR[START_DATE] and Dates[Date] <= GLYEAR[START_DATE] then FRL_FISCAL_YEAR

 

There is no existing relationship between Dates and GLYEAR.

 

Appreciate any pointers.

  • This seems like it's giving me the results I need.  Does anyone see any issues with this solution?

     

    Fiscal Year = CALCULATE(VALUES(GLYEAR[FISCAL_YEAR]), FILTER(GLYEAR, Dates[Date] >= GLYEAR[START_DATE] && Dates[Date] <= GLYEAR[END_DATE]))

2 Replies

  • This seems like it's giving me the results I need.  Does anyone see any issues with this solution?

     

    Fiscal Year = CALCULATE(VALUES(GLYEAR[FISCAL_YEAR]), FILTER(GLYEAR, Dates[Date] >= GLYEAR[START_DATE] && Dates[Date] <= GLYEAR[END_DATE]))
    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee

      Hi ChrisWilliams,

       

      According to your description, the formula provided above should work in this scenario.:smileyhappy:

       

      In addition, could you accept your reply as solution to help others who has similar issue easily find the answer and close this thread?

       

      Regards