Change Week Date format

[MarcF]

Hi,

Is it posisble to change the default US week date format? For instance, I would like to have the first week coinciding with the beginning of my fiscal year (ie: 01/07 instead of 01/01) and have the week span from Monday to Sunday instead of Sunday to Saturday. 

I currently use a Date Table with a column for week number and fiscal year / month  number.

Thank you

[ankitpatira]

@MarcF You need to leverage DAX to be able to do that. 

1. To get week starting Monday to Sunday use below code.

=WEEKDAY([dateColumn], 2)

2. To get fiscal year first create 2 new column as below.

Month = MONTH(Table[Date])

Year = YEAR(Table[Date])

Then create column for Fiscal Year as

Fiscal Year = IF(AND([Year] =2012,[Month]<=12),"FY13",

IF(AND([Year] =2013,[Month]<=6),"FY13",

IF(AND([Year] =2013,[Month]<=12),"FY14",        

IF(AND([Year] =2014,[Month]<=6),"FY14",

IF(AND([Year] =2014,[Month]<=12),"FY15",

IF(AND([Year] =2015,[Month]<=6),"FY15",

IF(AND([Year] =2015,[Month]<=12),"FY16",

IF(AND([Year] =2016,[Month]<=6),"FY16",

IF(AND([Year] =2016,[Month]<=12),"FY17",

IF(AND([Year] =2017,[Month]<=6),"FY17",

IF(AND([Year] =2017,[Month]<=12),"FY18",

" ")))))))))))

[MarcF]

Thanks for these tips. However, I already have a fiscal year column and what I would like to do is to start the week counting from the beginning of the fiscal year as opposed to the calendar year. Basically, the weekday column needs to start at a fixed date. How could I do that?

[Anonymous]

Hi @MarcF,

I am not sure which day your fiscal year start on. I made a test as follows, Assume that my fiscal year starts on Sunday June 19, 2016, I calculate the week numbers based on fiscal year using the following steps.

1. Create a new calendar table using the following formula in Power BI Desktop and use Year() function to calculate Calendar year.
Calendar = CALENDAR (DATE (2016,1, 1), DATE (2018, 12, 31))

2. Create a new column named Fiscal year using the formula below.
Fiscal year = If((Month([Date]) >= 6 && Format([Date],"MMDD") >= "0619") , 'Calendar'[Calendar year],'Calendar'[Calendar year] -1 )

3. Create  a new column called StartofYear.
StartofYear = "19/06/"&Format('Calendar'[Fiscal year],"####")

4.Calculate week number of fiscal year using the following formula.
FiscalYearWeekNumber = CEILING((WEEKDAY([StartofYear])+[Date]-[StartofYear])/7,2)

For more details, you can review the  example in the attached PBIX file. You may need to make some changes to the above formulas based on your data.

Reference:
https://community.powerbi.com/t5/Desktop/Fiscal-Year-Calculated-Columns/td-p/50711

Thanks,
Lydia Zhang