Forum Discussion
Helper VCustom Fiscal Year with DAX
Good morning,
I have a very large data set spanning 7 fiscal years. I currently have an area chart that visualizes "Total Screens" on the Y-axis and "Year" on the X-axis. See below:
These "Screens" are currently grouped by the calendar year.
I would like to create a custom fiscal year that runs from 10/1/2016 to 9/30/17, 10/1/2017-9/30/2018, etc.
I would then like to put those custom years in place of the "Year" that is currently seen above.
How do you create a custom fiscal year with DAX? I would also like to name each year Contract Year 2017, Contract Year 2018, etc.
Also, all of the data in this model is in a single table called "Appended Fiscals." The column name that hold the dates I want organized is called "Screened Date."
Any help would be appreicated.
chonchar if it is of type date then try to change it to this to see if it works:
Year = VAR __Date = 'Table'[Date] VAR __Year = YEAR ( __Date ) VAR __StartDate = DATE ( __Year, 10, 1 ) VAR __EndDate = DATE ( __Year + 1, 9, 30 ) RETURN IF ( __Date >= __StartDate && __Date <= __EndDate, __Year + 1, __Year )
14 Replies
- amitchandak
Super User
chonchar , I created few scripts in past for FY year, please check if those can help
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Power BI Date Table: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=18180s- chonchar
Thanks for the reply. I was hoping for somehting a little simpler to implement.
Something like:
If (screened date) ISBETWEEN 10/1/2016-9/30/2017, Return "Contract Year 2017", Else
Contract Year 2018
etc.
- parry2k
chonchar Logically you should add a calendar table for the fiscal period as suggested by amitchandak but if you ask is just to add this contract year column then you can use following to add this column:
Year = VAR __Date = 'Table'[Date] VAR __Year = YEAR ( __Date ) VAR __StartDate = DATE ( __Year, 10, 1 ) VAR __EndDate = DATE ( __Year + 1, 9, 30 ) RETURN "Contract Year " & IF ( __Date >= __StartDate && __Date <= __EndDate, __Year + 1, __Year )- chonchar
Thanks. I am running into the below issue.
I don't entirely understand the calendar table solution.
Does that basically assign the dates 10/1/2016-9/30/2017 to the year 2017, 10/1/2017-9/30/2018 to the year 2018, etc?
I have been on YouTube all morning trying to understand the distinctions.
- parry2k
chonchar I have no idea what those videos are? amitchandak can answer that better.
Regarding the expression error, what is the data type of screen by column?
- parry2k
chonchar if it is of type date then try to change it to this to see if it works:
Year = VAR __Date = 'Table'[Date] VAR __Year = YEAR ( __Date ) VAR __StartDate = DATE ( __Year, 10, 1 ) VAR __EndDate = DATE ( __Year + 1, 9, 30 ) RETURN IF ( __Date >= __StartDate && __Date <= __EndDate, __Year + 1, __Year ) - chonchar
= Table.TransformColumnTypes(#"Appended Query",{{"Screened Date ", type datetime}})
