I would like to calculate a new column(or measurement) called “CurrentFiscalYearCutOff” to indicate the current fiscal year, previous fiscal year, Previous-2 fiscal year.
CurrentFiscalYearCutoff=0, current fiscal year
CurrentFiscalYearCutoff=-1, previous fiscal year
CurrentFiscalYearCutOff=-2, previous -2 fiscal year
For information the Fiscal year starts on November 1st and ended on Oct 31.
Pls find below two examples with more details:
One more thing, the fact table (cases) and Dates table have inactive relationships but I am not sure whether this matter when we calculate the “CurrentFiscalYearCutOff”.
I would like to calculate the same for 'CurrentFiscalQuarterCutoff" to identify the current fiscal quarter, previous fiscal quarter and previous -2 fiscal quarter. Your help will be appreciated. Thank you.