OFFSET for Fiscal Year in Date Table (via DAX)

[jcamintoy]

Hello 🙂

Would like to seek help for adding Fiscal Offset Year, Quarter, Month and Day. 

Below is what I currently have and wondering if how I can add the above to my DAX - start of Fiscal Year for us is July. Appreciate the help. Thanks

Date Table =

var _fromYear=2019 // set the start year of the date dimension. dates start from 28th of December of this year

var _toYear=2022 // set the end year of the date dimension. dates end at 31st of December of this year

var _startOfFiscalYear=7 // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7

//**************

var _today=TODAY()

return

ADDCOLUMNS(

CALENDAR(

DATE(_fromYear,12,28),

DATE(_toYear,12,31)

),

"Year",YEAR([Date]),

"Start of Year",DATE( YEAR([Date]),12,28),

"End of Year",DATE( YEAR([Date]),12,31),

"Month",MONTH([Date]),

"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1),

"End of Month",EOMONTH([Date],0),

"Days in Month",DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,

"Year Month Number",INT(FORMAT([Date],"YYYYMM")),

"Year Month Name",FORMAT([Date],"YYYY-MMM"),

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Name Short",FORMAT([Date],"DDD"),

"Day of Week",WEEKDAY([Date]),

"Day of Year",DATEDIFF(DATE( YEAR([Date]), 1, 1),[Date],DAY)+1,

"Month Name",FORMAT([Date],"MMMM"),

"Month Name Short",FORMAT([Date],"MMM"),

"Quarter",QUARTER([Date]),

"Quarter Name","Q"&FORMAT([Date],"Q"),

"Year Quarter Number",INT(FORMAT([Date],"YYYYQ")),

"Year Quarter Name",FORMAT([Date],"YYYY")&" Q"&FORMAT([Date],"Q"),

"Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),

"End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),

"Week of Year",WEEKNUM([Date]),

"Start of Week", [Date]-WEEKDAY([Date])+1,

"End of Week",[Date]+7-WEEKDAY([Date]),

"Fiscal Year",if(_startOfFiscalYear=1,YEAR([Date]),YEAR([Date])+ QUOTIENT(MONTH([Date])+ (13-_startOfFiscalYear),13)),

"Fiscal Quarter",QUARTER( DATE( YEAR([Date]),MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,1) ),

"Fiscal Month",MOD( MONTH([Date])+ (13-_startOfFiscalYear) -1 ,12) +1,

"Day Offset",DATEDIFF(_today,[Date],DAY),

"Month Offset",DATEDIFF(_today,[Date],MONTH),

"Quarter Offset",DATEDIFF(_today,[Date],QUARTER),

"Year Offset",DATEDIFF(_today,[Date],YEAR)

)

[Anonymous]

Hi @jcamintoy ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[amitchandak]

@jcamintoy ,PLease find attached july to Jun calendar, if you do not start your calendar from july. You will not be able to use start of year and end of year for FY

Then you have to try

FY Start=

var _yr = if(month([Date]>6, [Year], [Year] -1)

return

date(_yr,7,1)

FY End=

var _yr = if(month([Date]>6, [Year]+1, [Year] )

return

date(_yr,6,30)

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...