Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AG_x11x
Frequent Visitor

achieving current financial year to date in DAX

Hi friends,

 

I need a DAX formula for my Custom Financial year.

 

I would like to add a column called "Current_FY_to_Date" showing:

my financial year starts 1st July and ends 30th June of the next year (e.g 1st July 2022 - 30 june 2023) and dax formula should return TRUE only for dates within my current financial year to date. 

 

any suggestions on how to achieve this would be appreciated. 

Current script: 

Central_Calendar = ADDCOLUMNS(

CALENDAR(DATE(2022,1, 1), DATE(2030,1,1))

, "Year", YEAR([DATE])

, "MonthNumber", MONTH([DATE])

, "Month", FORMAT([DATE], "MMM")

, "Quarter", "Q " & FORMAT([DATE], "Q")

, "QuarterNumber", FORMAT([DATE], "Q")

, "Year Quarter", YEAR([Date]) & " " & "Q" & QUARTER([Date])

, "Year Quarter Sort", YEAR([Date]) & QUARTER([Date])

, "YearMonthNumber", FORMAT([DATE], "yyyy-mm")

, "Month Year", FORMAT([DATE], "MMM YY")

, "Month Year Sort", YEAR([Date]) *100 + MONTH([Date])

, "Fiscal Year", YEAR(EDATE([DATE], 6))

, "FY", "FY" & IF(MONTH([Date])<7,

YEAR([Date])-1 & "/" & FORMAT([Date], "YY"), YEAR([Date]) & "/" & FORMAT(EOMONTH([DATE], 6), "YY"))

, "Fiscal Month", MONTH(EDATE([DATE], 6))

, "Fiscal Quarter", QUARTER(EDATE([DATE], 6))

, "Fiscal Week", WEEKNUM(EDATE([DATE], 6))

, "Last 6 Months", IF(EOMONTH([DATE], 0) > EOMONTH(TODAY(), -6) && [date] <= TODAY(), TRUE())

, "Current_FY_to_Date", IF([DATE] >= DATE(YEAR(TODAY()) -1, 7, 1) && [DATE] <= TODAY(),TRUE(), FALSE())

)


My current code is showing previous financial year up to year to date, not only current FY to date.

 

Thanks

2 ACCEPTED SOLUTIONS
FreemanZ
Super User
Super User

hi @AG_x11x ,

 

not sure if i fully get you, try like:

Central_Calendar2 = 
ADDCOLUMNS(
    CALENDAR(DATE(2022,1, 1), DATE(2030,1,1)), 
    "Current_FY_to_Date", 
    VAR _month = MONTH(TODAY())
    VAR _year = YEAR(TODAY())    
    VAR _fy = IF(_month>=7, _year+1, _year)
    VAR _monthrow = MONTH([date])
    VAR _yearrow =YEAR([date])
    VAR _fyrow = IF(_monthrow>=7,_yearrow+1,_yearrow)
    VAR _result = IF(_fy=_fyrow, TRUE, FALSE)    
    RETURN _result
)

 

it worked like:

FreemanZ_0-1699843393900.png

 

View solution in original post

AG_x11x
Frequent Visitor

Alternatively this script seems to work - please let us know if you see an enhancement or edit: "current_fy_to_Date", IF([date] >= DATE(YEAR(TODAY()), 7, 1) && [DATE] <= TODAY(), TRUE(), FALSE()) 

View solution in original post

2 REPLIES 2
AG_x11x
Frequent Visitor

Alternatively this script seems to work - please let us know if you see an enhancement or edit: "current_fy_to_Date", IF([date] >= DATE(YEAR(TODAY()), 7, 1) && [DATE] <= TODAY(), TRUE(), FALSE()) 

FreemanZ
Super User
Super User

hi @AG_x11x ,

 

not sure if i fully get you, try like:

Central_Calendar2 = 
ADDCOLUMNS(
    CALENDAR(DATE(2022,1, 1), DATE(2030,1,1)), 
    "Current_FY_to_Date", 
    VAR _month = MONTH(TODAY())
    VAR _year = YEAR(TODAY())    
    VAR _fy = IF(_month>=7, _year+1, _year)
    VAR _monthrow = MONTH([date])
    VAR _yearrow =YEAR([date])
    VAR _fyrow = IF(_monthrow>=7,_yearrow+1,_yearrow)
    VAR _result = IF(_fy=_fyrow, TRUE, FALSE)    
    RETURN _result
)

 

it worked like:

FreemanZ_0-1699843393900.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.