Forum Discussion

chonchar's avatar
chonchar
Icon for Helper V rankHelper V
2 years ago
Solved

Custom Fiscal Year with DAX

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 bel...
Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    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's avatar
parry2k
Icon for Super User rankSuper User
2 years ago

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's avatar
    chonchar
    Icon for Helper V rankHelper V
    2 years ago

    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.