Current Financial Year Offset - DAX

[Anonymous]

Hello everyone,

I am having hard time creating a current financial year offset column in a date table. Our financial year starts on November and ends on October. 

I would like to create a current financial year offset column that will show 0 if the months fall in current financial year, 1 if they fall in next financial year, -1 if they fall in previous financial year, and so on. 

I was able to create current week offset and current month offset but have been struggling to create current financial year offset. 

Can I please get some help with this? 

[Anonymous]

Here is an example of what it should look like - 

Example:

Calander Year	Financial Year	Month	Current Fin Year Offset
2022	2022	July	-1
2022	2022	August	-1
2022	2022	September	-1
2022	2022	October	-1
2022	2023	November	0
2022	2023	December	0
2023	2023	January	0
2023	2023	February	0
2023	2023	March	0
2023	2023	April	0
2023	2023	May	0
2023	2023	June	0
2023	2023	July	0
2023	2023	August	0
2023	2023	September	0
2023	2023	October	0
2023	2024	November	1
2023	2024	December	1
2024	2024	January	1

[v-yueyunzh-msft]

Hi , @Anonymous 

According to youe description, you want to add a flag column in your table .

Here are the steps you can refer to 

(1)This is  my test data:

(2)We can click "New Column" to add two columns like this:

Month Value = SWITCH([Month],"January",1,
"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)

Current Fin Year Offset = var _today = TODAY()
 var _cur_year =  YEAR( _today)
 return
SWITCH(TRUE(),
OR([Calander Year]=_cur_year && [Month Value]<=10, [Calander Year]=_cur_year-1 && [Month Value]>=11),0,
OR([Calander Year]=_cur_year && [Month Value]>=11, [Calander Year]=_cur_year+1 && [Month Value]<=10),1,
OR([Calander Year]=_cur_year-1 && [Month Value]<=10, [Calander Year]=_cur_year-2 && [Month Value]>=11),-1
)

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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