How to add calculated column in date table to show if date is in last 2 financial years

[Rob_Morris]

I've got a date table based on the following code:

Date =

GENERATE (

CALENDAR (min('All Jobs - Current Month'[actual_on_site]), max('All Jobs - Current Month'[actual_on_site])),

VAR currentDay = [Date]

VAR day = DAY(currentDay)

VAR month = MONTH (currentDay)

VAR quarter = QUARTER(currentDay)

VAR year = YEAR (currentDay)

VAR FinMonth = if (len(if(month<4,month+9,month-3))=1,"0"&if(month<4,month+9,month-3),if(month<4,month+9,month-3))

VAR FinQuarter = if(quarter=1,4,quarter-1)

VAR FinYear = if(month<4,year-1,year)

RETURN ROW (

"Year", YEAR ([Date]),

    "Month", FORMAT ([Date], "mmmm"),

    "Month Number", MONTH ([Date]),

    "Quarter", FORMAT ([Date], "\QQ"),

    "YY-MM", FORMAT ([Date], "YY-MM"),

"Fin Month", ""&FinMonth,

"Fin Quarter", "Q " & FinQuarter,

"Fin Year", FinYear )

)

What I would like to do is add another column to this that looks at the date (based on the 'All Jobs - Current Month'[actual_on_site] value) and assesses whether this is in the last 2 financial years (current financial year and previous) or not (with financial year running from 1st April - 31st March)

So a date of 1st April 2021 would be in the last 2 financial years. a date of 31st March 2021 would not.

[v-rongtiep-msft]

Hi @Rob_Morris ,

I have created a simple sample, please refer to it to see if it helps you.

Create a column.

Column = var _this=YEAR(TODAY())
return
IF(_this-2<='Table'[Year],"Yes","No")

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privay information.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rob_Morris]

@v-rongtiep-msft Thanks for this however it doesn't seem to work for the financial year.

So date 31st March 2021 is showing as in the last 2 years - when it should be showing as no.

Current financial year    1st April 2022 - 31st March 2023

Previous financial year  1st April 2021 - 31st March 2022

[v-rongtiep-msft]

Hi @Rob_Morris ,

Please modify the column.

Column = var _this=YEAR(TODAY())
return
IF(_this-1<='Table'[Year],"Yes","No")

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Rob_Morris , a new column

if([Date]>= if( Month(_max) <4 , date(year(_max)-2,4,1) ,date(year(_max)-1,4,1)) && [Date]<= if( Month(_max) <4 , date(year(_max)-1,3,31) ,date(year(_max),3,1)) , 1, 0)