achieving current financial year to date in DAX

[AG_x11x]

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

[FreemanZ · Accepted solution]

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:

[AG_x11x · Accepted solution]

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 · Accepted solution]

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: