Using a Day of Year for a Fiscal Year

[cmgish]

My organization operates on a fiscal year (June - May), and I need to create a custom column in Power Query that will give me the Day of the Year with June 1st being the first day of the year. I should say that I don't fully understand M code. I am learning a little, but am still a novice at this. Here is a screenshot of my Calendar query:

I appreciate any help I can get. I really want to learn how to do more custom work in Power Query, so if anyone also has some site suggestions on the best way to learn M code, that would also be appreciated. Thank you!

Chris

 

[James006]

Hi,
For the fiscal "day" formula, you could test

=TODAY()-DATE(IF(MONTH(TODAY())<6,YEAR(TODAY())-1,YEAR(TODAY())),6,1)

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Result = Table.AddColumn(tbl, "Day of Fiscal Year", each Date.DayOfYear([Date])- Date.DayOfYear(#date(Date.Year([Date]),5,31)))
in
    Result

Book2
	A	B	C	D	E
1	Date		Date	Day of Fiscal Year
2	6/1/2023		6/1/2023	1
3	6/2/2023		6/2/2023	2
4	6/3/2023		6/3/2023	3
5	6/4/2023		6/4/2023	4
6	6/5/2023		6/5/2023	5
7	6/6/2023		6/6/2023	6
8	6/7/2023		6/7/2023	7
9
Sheet2

 

[jdellasala]

This code comes from this video.

let
   //courtesy of AccessAnalytic.com.au


    Today = Date.From(DateTime.LocalNow() ),  

   // Change start date to begining of year
    StartDate= #date(2022, 1, 1),        
    
   //see advanced editor for note on alternative hardcoding EndDate method
    YearsInFuture = 0,
    EndDate = Date.EndOfYear(Date.AddYears(Today,YearsInFuture )),

    // Or comment out the 2 lines above replace with manual End Date below.. use end of year   
    //EndDate = #date(2021, 12, 31), 
    PointlessStepAddedToBreakCommentInAdvancedEditor = "",


    //set this as the last month number of your fiscal year : June = 6, July =7 etc
    MonthNumberForEndFinancialYear = 6,

   // Change to Day.Sunday or Day.Tuesday etc to impact the sort order number so you can then display your days in your visuals in the preferred way
    FirstDayOfWeek = Day.Monday,   



    DateList = {Number.From(StartDate)..Number.From(EndDate)},
    

    ConvertedDateListToTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedDate = Table.RenameColumns(ConvertedDateListToTable,{{"Column1", "Date"}}),
    ChangedType1 = Table.TransformColumnTypes(RenamedDate,{{"Date", type date}}),
    InsertedYear = Table.AddColumn(ChangedType1, "Year", each Date.Year([Date]), type number),
    InsertedMonthNumber = Table.AddColumn(InsertedYear, "Month Number", each Date.Month([Date]), type number),
    InsertedShortMonthName = Table.AddColumn(InsertedMonthNumber, "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    InsertedShortDayName = Table.AddColumn(InsertedShortMonthName, "Day", each Text.Start( Date.DayOfWeekName([Date]),3), type text),
    InsertedDayOfWeekNumberMon0 = Table.AddColumn(InsertedShortDayName, "Day of Week", each Date.DayOfWeek([Date],FirstDayOfWeek), Int64.Type),
    InsertedQuarterNumber = Table.AddColumn(InsertedDayOfWeekNumberMon0, "QuarterNumber", each Date.QuarterOfYear([Date]),Int64.Type),
    ChangedType2 = Table.TransformColumnTypes(InsertedQuarterNumber,{{"QuarterNumber", type text}, {"Year", type text}}),
    AddedQuarterNumberQx = Table.AddColumn(ChangedType2, "Quarter", each "Q"&[QuarterNumber], type text),
    RemovedQuarterDigit = Table.RemoveColumns(AddedQuarterNumberQx,{"QuarterNumber"}),
    AddedYYQQ = Table.AddColumn(RemovedQuarterDigit, "YY-QQ", each Text.End( [Year],2) & "-"& [Quarter], type text),
    ChangedType4 = Table.TransformColumnTypes(AddedYYQQ,{{"YY-QQ", type text}, {"Year", Int64.Type}}),
    #"▶ DatesSinceTodayFields" = ChangedType4,
    DateToday = Today,
    DaysAgo = Table.AddColumn(#"▶ DatesSinceTodayFields", "Days Since Today", each Duration.Days([Date] -  DateToday), Int32.Type),
    MonthsAgo = Table.AddColumn(DaysAgo, "Months Since Today", each ([Year] * 12 + [Month Number]) - (Date.Year(DateToday ) * 12 + Date.Month(DateToday )), Int32.Type),
    YearsAgo = Table.AddColumn(MonthsAgo, "Years Since Today", each [Year] - Date.Year(DateToday ), Int32.Type),
    #"◀ DatesSinceToday" = YearsAgo,
    #"▶FinancialYearCalcs" = #"◀ DatesSinceToday",
    FYMonthNumber = Table.AddColumn(#"▶FinancialYearCalcs", "Financial Month Number", each if [Month Number] > MonthNumberForEndFinancialYear  then [Month Number]-MonthNumberForEndFinancialYear  else 12-MonthNumberForEndFinancialYear+[Month Number]),
    ChangedType5 = Table.TransformColumnTypes(FYMonthNumber,{{"Financial Month Number", Int64.Type}}),
    FinancialYearEnd = Table.AddColumn(ChangedType5, "Financial Year End", each if [Financial Month Number] <=12-MonthNumberForEndFinancialYear  then [Year]+1 else [Year]),
    FiancialYearStart = Table.AddColumn(FinancialYearEnd, "Financial Year Start", each [Financial Year End] - 1, type number),
    ChangedType6 = Table.TransformColumnTypes(FiancialYearStart,{{"Financial Year End", type text}, {"Financial Year Start", type text}}),
    AddedFinancialYearRange = Table.AddColumn(ChangedType6, "Financial Year", each Text.End( [Financial Year Start],2) & "-" & Text.End([Financial Year End],2)),
    RemovedColumns1 = Table.RemoveColumns(AddedFinancialYearRange,{"Financial Year End", "Financial Year Start"}),
    // To work out Financial Quarter
    DivideFinancialMonthBy3 = Table.AddColumn(RemovedColumns1, "Financial Qtr Number", each [Financial Month Number] / 3, type number),
    RoundedUpToGetQuarter = Table.TransformColumns(DivideFinancialMonthBy3,{{"Financial Qtr Number", Number.RoundUp, Int64.Type}}),
    AddedFinancialQuarter = Table.AddColumn(RoundedUpToGetQuarter, "Financial Quarter", each "FQ-"&Text.From([Financial Qtr Number])),
    RemovedFyqHelper = Table.RemoveColumns(AddedFinancialQuarter,{"Financial Qtr Number"}),
    ChangedType7 = Table.TransformColumnTypes(RemovedFyqHelper,{{"Financial Quarter", type text}, {"Financial Year", type text}}),
    #"◀ FinancialYearCalcs" = ChangedType7,
    RenamedSortColumns = Table.RenameColumns(#"◀ FinancialYearCalcs",{{"Month Number", "Month Number for sort"}, {"Financial Month Number", "Financial Month Number for sort"}, {"Day of Week", "Day of Week for sort"}})
in
    RenamedSortColumns

It is huge, but by changing a few items in the query, you're set with a Calendar Table, especially needed for all date calculations, especially in Power Query and DAX. Here's a small sample of the results:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Year	Month Number for sort	Month	Day	Day of Week for sort	Quarter	YY-QQ	Days Since Today	Months Since Today	Years Since Today	Financial Month Number for sort	Financial Year	Financial Quarter
2	01/01/2022	2022	1	Jan	Sat	5	Q1	22-Q1	-429	-14	-1	7	21-22	FQ-3
3	01/02/2022	2022	1	Jan	Sun	6	Q1	22-Q1	-428	-14	-1	7	21-22	FQ-3
4	01/03/2022	2022	1	Jan	Mon	0	Q1	22-Q1	-427	-14	-1	7	21-22	FQ-3
5	01/04/2022	2022	1	Jan	Tue	1	Q1	22-Q1	-426	-14	-1	7	21-22	FQ-3
Sheet1

It's a bit more than requested, but a great tool to have!