How to identify Next Fiscal Year through DAX

[Anonymous]

Hi Team,

I need your help in creating one of the DAX.

Requirement: I have to build a column where I can have Fiscal Year from 2019 to Current Year + 1

Time Table

Date	FY	Actual or Forecast	Fiscal Year	Expected Column
1-Oct-17	FY18	Actuals	2018
1-Nov-17	FY18	Actuals	2018
1-Dec-17	FY18	Actuals	2018
1-Jan-18	FY18	Actuals	2018
1-Feb-18	FY18	Actuals	2018
1-Mar-18	FY18	Actuals	2018
1-Apr-18	FY18	Actuals	2018
1-May-18	FY18	Actuals	2018
1-Jun-18	FY18	Actuals	2018
1-Jul-18	FY18	Actuals	2018
1-Aug-18	FY18	Actuals	2018
1-Sep-18	FY18	Actuals	2018
1-Oct-18	FY19	Actuals	2019	FY19
1-Nov-18	FY19	Actuals	2019	FY19
1-Dec-18	FY19	Actuals	2019	FY19
1-Jan-19	FY19	Actuals	2019	FY19
1-Feb-19	FY19	Actuals	2019	FY19
1-Mar-19	FY19	Actuals	2019	FY19
1-Apr-19	FY19	Actuals	2019	FY19
1-May-19	FY19	Actuals	2019	FY19
1-Jun-19	FY19	Actuals	2019	FY19
1-Jul-19	FY19	Actuals	2019	FY19
1-Aug-19	FY19	Actuals	2019	FY19
1-Sep-19	FY19	Actuals	2019	FY19
1-Oct-19	FY20	Actuals	2020	FY20
1-Nov-19	FY20	Actuals	2020	FY20
1-Dec-19	FY20	Actuals	2020	FY20
1-Jan-20	FY20	Actuals	2020	FY20
1-Feb-20	FY20	Actuals	2020	FY20
1-Mar-20	FY20	Actuals	2020	FY20
1-Apr-20	FY20	Actuals	2020	FY20
1-May-20	FY20	Actuals	2020	FY20
1-Jun-20	FY20	Actuals	2020	FY20
1-Jul-20	FY20	Actuals	2020	FY20
1-Aug-20	FY20	Actuals	2020	FY20
1-Sep-20	FY20	Actuals	2020	FY20
1-Oct-20	FY21	Actuals	2021	FY21
1-Nov-20	FY21	Actuals	2021	FY21
1-Dec-20	FY21	Actuals	2021	FY21
1-Jan-21	FY21	Actuals	2021	FY21
1-Feb-21	FY21	Actuals	2021	FY21
1-Mar-21	FY21	Actuals	2021	FY21
1-Apr-21	FY21	Actuals	2021	FY21
1-May-21	FY21	Actuals	2021	FY21
1-Jun-21	FY21	Actuals	2021	FY21
1-Jul-21	FY21	Actuals	2021	FY21
1-Aug-21	FY21	Actuals	2021	FY21
1-Sep-21	FY21	Actuals	2021	FY21
1-Oct-21	FY22	Actuals	2022	FY22
1-Nov-21	FY22	Actuals	2022	FY22
1-Dec-21	FY22	Actuals	2022	FY22
1-Jan-22	FY22	Actuals	2022	FY22
1-Feb-22	FY22	Actuals	2022	FY22
1-Mar-22	FY22	Actuals	2022	FY22
1-Apr-22	FY22	Actuals	2022	FY22
1-May-22	FY22	Actuals	2022	FY22
1-Jun-22	FY22	Actuals	2022	FY22
1-Jul-22	FY22	Actuals	2022	FY22
1-Aug-22	FY22	Forecast	2022	FY22
1-Sep-22	FY22	Forecast	2022	FY22
1-Oct-22	FY23	Forecast	2023	FY23
1-Nov-22	FY23	Forecast	2023	FY23
1-Dec-22	FY23	Forecast	2023	FY23
1-Jan-23	FY23	Forecast	2023	FY23
1-Feb-23	FY23	Forecast	2023	FY23
1-Mar-23	FY23	Forecast	2023	FY23
1-Apr-23	FY23	Forecast	2023	FY23
1-May-23	FY23	Forecast	2023	FY23
1-Jun-23	FY23	Forecast	2023	FY23
1-Jul-23	FY23	Forecast	2023	FY23
1-Aug-23	FY23	Forecast	2023	FY23
1-Sep-23	FY23	Forecast	2023	FY23
1-Oct-23	FY24	Forecast	2024
1-Nov-23	FY24	Forecast	2024
1-Dec-23	FY24	Forecast	2024
1-Jan-24	FY24	Forecast	2024
1-Feb-24	FY24	Forecast	2024
1-Mar-24	FY24	Forecast	2024
1-Apr-24	FY24	Forecast	2024
1-May-24	FY24	Forecast	2024
1-Jun-24	FY24	Forecast	2024
1-Jul-24	FY24	Forecast	2024
1-Aug-24	FY24	Forecast	2024
1-Sep-24	FY24	Forecast	2024

The problem I am facing is that Fiscal Year and FY columns are Whole number and text columns. Please help.

[jdbuchanan71 · Accepted solution]

Give this a try.

New Column = 
VAR _NextFY =
    CALCULATE (
        MAX ( 'Calendar'[Fiscal Year] ),
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= TODAY ()
    ) + 1
RETURN
    IF (
        AND ( [Fiscal Year] >= 2019, [Fiscal Year] <= _NextFY ),
        "FY" & ( [Fiscal Year] - 2000 )
    )

[jdbuchanan71]

Try something like this.

New Column = IF ( AND ( [Fiscal Year] >= 2019, [Fiscal Year] <= 2023 ), "FY" & ( [Fiscal Year] - 2000 )

[Anonymous]

Hi @jdbuchanan71 ,

This will work in a senario when we have this table fixed but going forward the data will increase for upcoming month. 
Instead of hardcoding it to 2023 can we figure out a way to check current FY + 1?