cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Manisha_91
Frequent Visitor

How to identify Next Fiscal Year through DAX

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

DateFYActual or ForecastFiscal YearExpected Column
1-Oct-17FY18Actuals2018 
1-Nov-17FY18Actuals2018 
1-Dec-17FY18Actuals2018 
1-Jan-18FY18Actuals2018 
1-Feb-18FY18Actuals2018 
1-Mar-18FY18Actuals2018 
1-Apr-18FY18Actuals2018 
1-May-18FY18Actuals2018 
1-Jun-18FY18Actuals2018 
1-Jul-18FY18Actuals2018 
1-Aug-18FY18Actuals2018 
1-Sep-18FY18Actuals2018 
1-Oct-18FY19Actuals2019FY19
1-Nov-18FY19Actuals2019FY19
1-Dec-18FY19Actuals2019FY19
1-Jan-19FY19Actuals2019FY19
1-Feb-19FY19Actuals2019FY19
1-Mar-19FY19Actuals2019FY19
1-Apr-19FY19Actuals2019FY19
1-May-19FY19Actuals2019FY19
1-Jun-19FY19Actuals2019FY19
1-Jul-19FY19Actuals2019FY19
1-Aug-19FY19Actuals2019FY19
1-Sep-19FY19Actuals2019FY19
1-Oct-19FY20Actuals2020FY20
1-Nov-19FY20Actuals2020FY20
1-Dec-19FY20Actuals2020FY20
1-Jan-20FY20Actuals2020FY20
1-Feb-20FY20Actuals2020FY20
1-Mar-20FY20Actuals2020FY20
1-Apr-20FY20Actuals2020FY20
1-May-20FY20Actuals2020FY20
1-Jun-20FY20Actuals2020FY20
1-Jul-20FY20Actuals2020FY20
1-Aug-20FY20Actuals2020FY20
1-Sep-20FY20Actuals2020FY20
1-Oct-20FY21Actuals2021FY21
1-Nov-20FY21Actuals2021FY21
1-Dec-20FY21Actuals2021FY21
1-Jan-21FY21Actuals2021FY21
1-Feb-21FY21Actuals2021FY21
1-Mar-21FY21Actuals2021FY21
1-Apr-21FY21Actuals2021FY21
1-May-21FY21Actuals2021FY21
1-Jun-21FY21Actuals2021FY21
1-Jul-21FY21Actuals2021FY21
1-Aug-21FY21Actuals2021FY21
1-Sep-21FY21Actuals2021FY21
1-Oct-21FY22Actuals2022FY22
1-Nov-21FY22Actuals2022FY22
1-Dec-21FY22Actuals2022FY22
1-Jan-22FY22Actuals2022FY22
1-Feb-22FY22Actuals2022FY22
1-Mar-22FY22Actuals2022FY22
1-Apr-22FY22Actuals2022FY22
1-May-22FY22Actuals2022FY22
1-Jun-22FY22Actuals2022FY22
1-Jul-22FY22Actuals2022FY22
1-Aug-22FY22Forecast2022FY22
1-Sep-22FY22Forecast2022FY22
1-Oct-22FY23Forecast2023FY23
1-Nov-22FY23Forecast2023FY23
1-Dec-22FY23Forecast2023FY23
1-Jan-23FY23Forecast2023FY23
1-Feb-23FY23Forecast2023FY23
1-Mar-23FY23Forecast2023FY23
1-Apr-23FY23Forecast2023FY23
1-May-23FY23Forecast2023FY23
1-Jun-23FY23Forecast2023FY23
1-Jul-23FY23Forecast2023FY23
1-Aug-23FY23Forecast2023FY23
1-Sep-23FY23Forecast2023FY23
1-Oct-23FY24Forecast2024 
1-Nov-23FY24Forecast2024 
1-Dec-23FY24Forecast2024 
1-Jan-24FY24Forecast2024 
1-Feb-24FY24Forecast2024 
1-Mar-24FY24Forecast2024 
1-Apr-24FY24Forecast2024 
1-May-24FY24Forecast2024 
1-Jun-24FY24Forecast2024 
1-Jul-24FY24Forecast2024 
1-Aug-24FY24Forecast2024 
1-Sep-24FY24Forecast2024 


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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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 )
    )

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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
Super User
Super User

Try something like this.

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

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?

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.