Forum Discussion

MarcF's avatar
MarcF
Advocate IV
9 years ago

Change Week Date format

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...
Need Help
MarcF's avatar
MarcF
Advocate IV
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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