Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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:
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())
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())
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:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
25 | |
16 | |
14 | |
13 |