Change Fiscal Year on Date Dimenson

[escalas]

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

[sdjensen · Accepted solution]

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.

[escalas]

Thanks for the detailed answer @sdjensen, works great. 

[Chinna99]

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

[escalas]

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