Identify Current Fiscal Year and Last Fiscal Year and return it as a single value

[yasemsem]

I want to create a measure that return current fiscal year and last fiscal year

I have the a table with fiscal calendar with column "FY" identifying the fiscal year of each date.

Fiscal Year usually start by end of Jan or beg of Feb.

Tried to create a measure which didn't accept column references. 
 

Created a new column, which return the "FY" if date.month =april(as example it could be any other month past Feb to make sure it is current FY and if Date.Year=now() . BUT it only returns the Current fiscal year of month April. I thought when I put it in a Var it will be returned a single value to be used in other calculations like Y/Y growth.

This Fiscal Year =

Var CFY=IF(YEAR(NOW())='Fiscal Calendar'[Calendar Date].[Year] && 'Fiscal Calendar'[Calendar Date].[Month]="April",'Fiscal Calendar'[FY])

return IF('Fiscal Calendar'[FY]=CFY,'Fiscal Calendar'[FY])

Attached PBI file

[yasemsem · Accepted solution]

Hello,

This was solved using lookupvalue. For some reason it was not working before and I tried again and now it is working

Current Fiscal Year  = lookupvalue('Table'[Fiscal Year], 'Table'[Calendar Date], Today())

Last Fiscal Year = lookupvalue('Table'[Fiscal Year], 'Table'[Calendar Date], Today()) - 1

[v-xinruzhu-msft]

Hi @yasemsem 

Do you mean that if the current date is in February or January, the last fiscal year is returned, and if after February and the year is current, this fiscal year is returned?

Best Regards!

Yolo Zhu

[yasemsem]

Thank you Yolo Zhu

Not really. What I mean is that the fiscal year dates are dynamic.. it can end for example by 31st Jan or by 4th of Feb.

So what I need is a measure to identify current fiscal year based on the "Fiscal Calendar" table in the PBI file uploaded to below drive link:

PBI:https://drive.google.com/file/d/19Ja95S5DrRNtQKAPb71mxG-epx_vXZfJ/view?usp=share_link 

The table has the dates and the equivalent Fiscal Year(FY) for this Date.

What I tried to do before is to know the fiscal year from anytime after Feb because I dont really know when the fiscal year will start as we said it is dynamix. Accordingly I set it to be taken from April as an example. But it did not work.

I hope this clarifies.

[yasemsem]

Screenshot:
https://drive.google.com/file/d/1jsU-t-lYMLNLbvDVaTphFcoCSkCu5oEO/view?usp=share_link 

PBI:
PBI:https://drive.google.com/file/d/19Ja95S5DrRNtQKAPb71mxG-epx_vXZfJ/view?usp=share_link