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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.