cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Change Fiscal Year on Date Dimenson

Hi everyone,

 

I've seen several post with this topic but still I am not clear about this. I use the code below that I found online and works perfectly creating a table with all the Date attributes but I want a Fiscal Year that start in April and finish in May, so that QuarterNumber and WeekNumber also start counting in April.

 

In the bold part of the code the columns  are created calling the Date function and my question is if you have any idea how I can modify these lines to get what want or if you know another way to do it. 

 

Thanks in advance.

 

(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount, 
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList, 
Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, "Year", 
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Month", 
each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
each Date.ToText([Date],"dddd"))
in
DayOfWeek

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Change Fiscal Year on Date Dimenson

The way I usually do this is by first creating a Parameter in my model called 'FiscalOffset' (Type = 'Decimal Number', Suggested Values = 'Any value', Current Value = 'The value I need to calculate the right Fiscal date columns'). In your case you should set this parameter to 3 (= the first 3 months of a calendar year belongs to a fiscal year that started in the previous calendar year).

 

The parameters isn't really needed, but I do this, because I am a consultant delivering models to many customers with different fiscal year start and this mean I only have to change the parameter instead of all the lines in my code.

 

Then add the code below to your existing code before the 'in' line. Remember to add a comma in the end of the line before (end of DayOfWeek step) and change the line after in to 'AddFiscalYearMonthSorting' (Name of the last step)

 

AddFiscalYearNumber = Table.AddColumn(DayOfWeek, "FiscalYearNumber", each Date.Year(Date.AddMonths([Dato], -FiscalOffset)), Int64.Type),
    AddFiscalYear = Table.AddColumn(AddFiscalYearNumber, "Fiscal Year", each Number.ToText(Date.Year(Date.AddMonths([Dato], -FiscalOffset))) & "/" & Number.ToText(Date.Year(Date.AddYears(Date.AddMonths([Dato], -FiscalOffset), 1)))),
    AddFiscalQuarterNumber = Table.AddColumn(AddFiscalYear, "FiscalQuarterNumber", each Date.QuarterOfYear(Date.AddMonths([Dato], -4)), Int64.Type),
    AddFiscalQuarter = Table.AddColumn(AddFiscalQuarterNumber, "Fiscal Quarter", each "Q" & Number.ToText([FiscalQuarterNumber])),
    AddFiscalMonthNumber = Table.AddColumn( AddFiscalQuarter, "FiscalMonthNumber", each Date.Month( Date.AddMonths( [Dato], -FiscalOffset ) ), Int64.Type ),
    AddFiscalMonth = Table.AddColumn(AddFiscalMonthNumber, "Fiscal Month", each Date.ToText([Dato],"MMM")),
    AddFiscalYearQuarter = Table.AddColumn(AddFiscalMonth, "FiscalYearQuarter", each "Q" & Number.ToText([FiscalQuarterNumber]) & " " & [Fiscal Year]),
    AddFiscalYearQuarterSorting = Table.AddColumn(AddFiscalYearQuarter, "FiscalYearQuarterSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalQuarterNumber]), 2)),
    AddFiscalYearMonth = Table.AddColumn(AddFiscalYearQuarterSorting, "FiscalYearMonth", each [Fiscal Month] & " " & Number.ToText([Year])),
    AddFiscalYearMonthSorting = Table.AddColumn(AddFiscalYearMonth, "FiscalYearMonthSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalMonthNumber]), 2))

 

Hide the following column in your model: FiscalYearNumber, FiscalQuarterNumber, FiscalMonthNumber, FiscalYearQuarter, FiscalYearQuarterSort, FiscalYearMonth, FiscalYearMonthSort.

 

Sort 'Fiscal Year' by FiscalYearNumber

Sort 'Fiscal Quarter' by FiscalQuarterNumber

Sort 'Fiscal Month' by FiscalMonthNumber

Sort FiscalYearQuarter by FiscalYearQuarterSort

Sort FiscalYearMonth by FiscalYearMonthSort

 

If you want a fiscal hierarchy you can then follow these steps:

Add Fiscal Year to a hierarchy (right click and select 'new hierarchy' - rename if you want)

Add FiscalYearQuarter to the hierarchy (rename if you want)

Add FiscalYearMonth to the hierarchy (rename if you want)

Add Date to the hierarchy (rename if you want)

 

When I build my models I call the hierarchy Fiscal Y-Q-M-D and the levels I call Fiscal Year, Fiscal Quarter, Fiscal Month and Fiscal Date. I do this so it clear which hierarchy I have use in my visuals, filters etc.

 

You should add an extra step where you change the datetype of some of the columns (left to the column name click the ABC 123 button and select the datatype). This is to get the correct sorting and to avoid Power BI to see some of the values as Numbers (Measures)

'Fiscal Year', 'Fiscal Quarter', 'Fiscal Month', FiscalYearQuarter, FiscalYearMonth should be set to 'Text'

FiscalYearQuarterSort, FiscalYearMonthSort should be set to should be set to 'Whole Number'

 

I hope this will help you.

/sdjensen

View solution in original post

4 REPLIES 4
Highlighted
Frequent Visitor

Re: Change Fiscal Year on Date Dimenson

Probably is not the best way but I have solved it with the conditional column function in the query editor. 

Highlighted
Solution Sage
Solution Sage

Re: Change Fiscal Year on Date Dimenson

The way I usually do this is by first creating a Parameter in my model called 'FiscalOffset' (Type = 'Decimal Number', Suggested Values = 'Any value', Current Value = 'The value I need to calculate the right Fiscal date columns'). In your case you should set this parameter to 3 (= the first 3 months of a calendar year belongs to a fiscal year that started in the previous calendar year).

 

The parameters isn't really needed, but I do this, because I am a consultant delivering models to many customers with different fiscal year start and this mean I only have to change the parameter instead of all the lines in my code.

 

Then add the code below to your existing code before the 'in' line. Remember to add a comma in the end of the line before (end of DayOfWeek step) and change the line after in to 'AddFiscalYearMonthSorting' (Name of the last step)

 

AddFiscalYearNumber = Table.AddColumn(DayOfWeek, "FiscalYearNumber", each Date.Year(Date.AddMonths([Dato], -FiscalOffset)), Int64.Type),
    AddFiscalYear = Table.AddColumn(AddFiscalYearNumber, "Fiscal Year", each Number.ToText(Date.Year(Date.AddMonths([Dato], -FiscalOffset))) & "/" & Number.ToText(Date.Year(Date.AddYears(Date.AddMonths([Dato], -FiscalOffset), 1)))),
    AddFiscalQuarterNumber = Table.AddColumn(AddFiscalYear, "FiscalQuarterNumber", each Date.QuarterOfYear(Date.AddMonths([Dato], -4)), Int64.Type),
    AddFiscalQuarter = Table.AddColumn(AddFiscalQuarterNumber, "Fiscal Quarter", each "Q" & Number.ToText([FiscalQuarterNumber])),
    AddFiscalMonthNumber = Table.AddColumn( AddFiscalQuarter, "FiscalMonthNumber", each Date.Month( Date.AddMonths( [Dato], -FiscalOffset ) ), Int64.Type ),
    AddFiscalMonth = Table.AddColumn(AddFiscalMonthNumber, "Fiscal Month", each Date.ToText([Dato],"MMM")),
    AddFiscalYearQuarter = Table.AddColumn(AddFiscalMonth, "FiscalYearQuarter", each "Q" & Number.ToText([FiscalQuarterNumber]) & " " & [Fiscal Year]),
    AddFiscalYearQuarterSorting = Table.AddColumn(AddFiscalYearQuarter, "FiscalYearQuarterSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalQuarterNumber]), 2)),
    AddFiscalYearMonth = Table.AddColumn(AddFiscalYearQuarterSorting, "FiscalYearMonth", each [Fiscal Month] & " " & Number.ToText([Year])),
    AddFiscalYearMonthSorting = Table.AddColumn(AddFiscalYearMonth, "FiscalYearMonthSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalMonthNumber]), 2))

 

Hide the following column in your model: FiscalYearNumber, FiscalQuarterNumber, FiscalMonthNumber, FiscalYearQuarter, FiscalYearQuarterSort, FiscalYearMonth, FiscalYearMonthSort.

 

Sort 'Fiscal Year' by FiscalYearNumber

Sort 'Fiscal Quarter' by FiscalQuarterNumber

Sort 'Fiscal Month' by FiscalMonthNumber

Sort FiscalYearQuarter by FiscalYearQuarterSort

Sort FiscalYearMonth by FiscalYearMonthSort

 

If you want a fiscal hierarchy you can then follow these steps:

Add Fiscal Year to a hierarchy (right click and select 'new hierarchy' - rename if you want)

Add FiscalYearQuarter to the hierarchy (rename if you want)

Add FiscalYearMonth to the hierarchy (rename if you want)

Add Date to the hierarchy (rename if you want)

 

When I build my models I call the hierarchy Fiscal Y-Q-M-D and the levels I call Fiscal Year, Fiscal Quarter, Fiscal Month and Fiscal Date. I do this so it clear which hierarchy I have use in my visuals, filters etc.

 

You should add an extra step where you change the datetype of some of the columns (left to the column name click the ABC 123 button and select the datatype). This is to get the correct sorting and to avoid Power BI to see some of the values as Numbers (Measures)

'Fiscal Year', 'Fiscal Quarter', 'Fiscal Month', FiscalYearQuarter, FiscalYearMonth should be set to 'Text'

FiscalYearQuarterSort, FiscalYearMonthSort should be set to should be set to 'Whole Number'

 

I hope this will help you.

/sdjensen

View solution in original post

Highlighted
Frequent Visitor

Re: Change Fiscal Year on Date Dimenson

Thanks for the detailed answer @sdjensen, works great. 

Highlighted
New Member

Re: Change Fiscal Year on Date Dimenson

 

 

 

Hi,

  I am new to scripting. I understand that I need to create 3 parameters, Next add this query . then what's next , how i can use this on date dimension.

 

Thanks

 

 

(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),
Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate),
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, "Year",
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , "Quarter",
each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , "Week Number",
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, "Month Number",
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Month",
each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , "Day of Week",
each Date.ToText([Date],"dddd")),
AddFiscalYearNumber = Table.AddColumn(DayOfWeek, "FiscalYearNumber", each Date.Year(Date.AddMonths([Dato], -FiscalOffset)), Int64.Type),
AddFiscalYear = Table.AddColumn(AddFiscalYearNumber, "Fiscal Year", each Number.ToText(Date.Year(Date.AddMonths([Dato], -FiscalOffset))) & "/" & Number.ToText(Date.Year(Date.AddYears(Date.AddMonths([Dato], -FiscalOffset), 1)))),
AddFiscalQuarterNumber = Table.AddColumn(AddFiscalYear, "FiscalQuarterNumber", each Date.QuarterOfYear(Date.AddMonths([Dato], -4)), Int64.Type),
AddFiscalQuarter = Table.AddColumn(AddFiscalQuarterNumber, "Fiscal Quarter", each "Q" & Number.ToText([FiscalQuarterNumber])),
AddFiscalMonthNumber = Table.AddColumn( AddFiscalQuarter, "FiscalMonthNumber", each Date.Month( Date.AddMonths( [Dato], -FiscalOffset ) ), Int64.Type ),
AddFiscalMonth = Table.AddColumn(AddFiscalMonthNumber, "Fiscal Month", each Date.ToText([Dato],"MMM")),
AddFiscalYearQuarter = Table.AddColumn(AddFiscalMonth, "FiscalYearQuarter", each "Q" & Number.ToText([FiscalQuarterNumber]) & " " & [Fiscal Year]),
AddFiscalYearQuarterSorting = Table.AddColumn(AddFiscalYearQuarter, "FiscalYearQuarterSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalQuarterNumber]), 2)),
AddFiscalYearMonth = Table.AddColumn(AddFiscalYearQuarterSorting, "FiscalYearMonth", each [Fiscal Month] & " " & Number.ToText([Year])),
AddFiscalYearMonthSorting = Table.AddColumn(AddFiscalYearMonth, "FiscalYearMonthSort", each Number.ToText([FiscalYearNumber]) & Text.End("0" & Number.ToText([FiscalMonthNumber]), 2))
in
AddFiscalYearMonthSorting

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors