how to add "Current Fiscal year" column in Fiscal Year calendar report in Power BI

[rahulmenon]

Hi Support team,

I need support in adding a dedicated column in my calendar table (Fiscal year calendar - May to April).

Calendar table have previous as well as future dates, I need to add a dedicated column which says "CY" for all dates for current FY (Current FY starts fom 1st May 2024 to 30th April 2025).

Can you experts help me to add a dedicated FISCAL CALENDAR and a dedicated column in it for Current Fiscal Year, and other values to be called as "Others"

thanks in advance. 

[v-nuoc-msft]

Hi @rahulmenon 

For your question, here is the method I provided:

Here's some dummy data

"FISCAL CALENDAR"

Create a column.

Current Fiscal Year = 
VAR CurrentFYStart = DATE(2024, 5, 1) 
VAR CurrentFYEnd = DATE(2025, 4, 30) 
RETURN
IF(
    AND(
        'FISCAL CALENDAR'[Date] >= CurrentFYStart,
        'FISCAL CALENDAR'[Date] <= CurrentFYEnd
    ),
    "CY", 
    "Others" 
)

Here is the result.

Regards,

Nono Chen

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

[rahulmenon]

Hi, thanks for your support. But in this case when the Fiscal Year changes, this measure will not work i guess. Correct me if I am wrong. I want a permanent column which is dynamic, should automatically able to change "CY" when the actual Fiscal Year changes.

[v-nuoc-msft · Accepted solution]

Hi @rahulmenon 

I recommend that you create a table for the current fiscal year.

current fiscal year = CALENDAR("5/1/2024", "4/30/2025")

Populate the new column by getting the largest and smallest dates for the current fiscal year.

Current Fiscal Year = 
VAR CurrentFYStart = MIN('current fiscal year'[Date]) 
VAR CurrentFYEnd = MAX('current fiscal year'[Date]) 
RETURN
IF(
    AND(
        'FISCAL CALENDAR'[Date] >= CurrentFYStart,
        'FISCAL CALENDAR'[Date] <= CurrentFYEnd
    ),
    "CY", 
    "Others" 
)

If your current fiscal year has changed, the new column should also change.

Regards,

Nono Chen

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