New Column Value Based on Date Range

[BradleyH]

I am trying add a new column for the "Year", based on a date range with a specified Start Date and End Date.

Some of the problems I am running into:

The "Year" has an unconventional Start and End Date. 

• Start = 5/1 - end = 4/30 

The "Year for a given product is 104 weeks. 

• For Example:
  • Year: "2015" 
    • Start = 5/1/2014 - End = 4/30/2016
• Due to the products having a 104-week year, they will always be over lapping with on another. 
  • Year: "2014"
    • Start = 5/1/2013 - End = 4/30/2015
  • Year: "2015"
    • Start = 5/1/2014 - End = 4/30/2016
  • Year: "2016"
    • Start = 5/1/2015 - End = 4/30/17

The result should look like this.

With the column header = "Product Year [Value in column]"

Other notes: My goal with this is to be able to use the "Product Year" to refrence the Date associated with it to pull data from tables with the same date format. (Cardnality is one to one). One table contains the 1st 52 week period and another table contains the 2nd. My assumption is I can use the values in the columns to complete lookups and build specific calcualtions based on the year.  

I am a long time Excel and Tableau User but am cutting my teeth with Power Bi and, I need help.

Thank you 

[amitchandak]

@BradleyH , seem like you want to create FY

FY = if( Month(([DAte])) <5 , year(([DAte]))-1 ,year(([DAte])))

Also refer

Creating Financial Calendar - From Any Month
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