Forum Discussion
Current Financial Year Offset - DAX
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?
2 Replies
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
Community Support
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
