Custom Fiscal Year with DAX

[chonchar]

@parry2k @Erokor 

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. 

[parry2k]

@chonchar you can always create some dummy data that mimics your production data and it doesn't need to have all the columns because in this case, only one column is relevant. Good luck!

[parry2k]

@chonchar there is something wrong with the data, very hard to tell what it is? Maybe share your pbix file.

[chonchar]

@parry2k Thanks for the help. I wish I could share it, but there is a ton of secret stuff in here for my job. I am just going to try and change the types until it works, else I will move on and reach out to someone internally. Thank you. 

[parry2k]

@chonchar you should try 2nd expression I sent to you and see if it works.

[chonchar]

@parry2k I tried it and it is not working

[parry2k]

@chonchar format and data type are two different things. What is the data type of this column? You can check it in PQ.

[chonchar]

= Table.TransformColumnTypes(#"Appended Query",{{"Screened Date ", type datetime}})

[parry2k · Accepted solution]

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

[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?

[chonchar]

@parry2k Date/Time fomatted as (MM/DD/YYYY)

[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]

@parry2k @amitchandak 

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.

[amitchandak]

@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-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
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]

@amitchandak 

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.