topic achieving current financial year to date in DAX in DAX Commands and Tips

achieving current financial year to date in DAX

AG_x11x — Sun, 12 Nov 2023 22:35:43 GMT

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

Re: achieving current financial year to date in DAX

FreemanZ — Mon, 13 Nov 2023 02:44:46 GMT

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:

Re: achieving current financial year to date in DAX

AG_x11x — Tue, 14 Nov 2023 01:22:09 GMT

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