I am using the Data query provided with the Enterprise DNA program, but looking to add a “Day of Year” or “Julian Date” column. I tried to add the line in the advanced editor, but the new column is not reflecting in the Date query.
Is it possible to add a “Day of Year” Column and if so, what is the best method?
They Fiscal year is also July 1st to June 30th. My Date query is also attached for reference.
Date Template - DayOfYear.pbix (510.2 KB)
Hi @Jackie,
In M you usually built on the result of the previous step.
So the InsertDay step is referenced twice, but you should change the highlighted on in red by InsertDatenYear then you’re good to go.
Hi @Jackie , did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
That worked! Thank you Melissa.
Is it possible to start the numbering on 7/1/2020 versus 1/1/2020 within M?
Sure just need a bit more to go on here…
What is the current situation and what is your goal?
Our reporting occurs in 13 - 4 week periods starting July 1st, ending June 30th. Once I had the ‘Day of Year’ established, I was going to run the math to calculate Periods by 28 days to account for leap years. The Periods will also need to account for the same Periods YOY when running comparisons.
I’ve looked at other forums for direction to set up the initial queries, but I’m not seeing anything like I need. Any help is appricated!
You’re in luck. It just so happens that I answered a question about 13 period fiscal calendars yesterday and created a topic on that, you can find it here.
Can you see if that meets your requirement.
This is perfect! Thank you very much.
I have another level to add to this inquiry. I thought it was going to be as simple as 13 - 4 week periods, but it actually rotates 4 weeks, 4 weeks, 5 weeks. Is this code possible?
Week ends on Saturday and follow 4 weeks, 4 weeks, 5 weeks = 13 weeks in a quarter.
1st month of qtr. = 4 weeks
2nd month of qtr.= 4 weeks
3rd month of qtr. = 5 weeks
Dates for examples:
July 4 (fiscal 2020 year end) July 5 (start of fiscal 2021)
August 1
August 29
Oct. 3
Oct. 31
Nov. 28
Jan. 2 … etc.
Fiscal 2022 would start July 3, 2021
Hi @Jackie,
Can you check this code?
Thanks
let fnDateTable = ( FirstFYDate as date, optional StartOfWeekDay as number, optional AddYears as number, optional Holidays as list ) as table =>
let
FYStartDate = FirstFYDate, // #date(2019, 9, 29), // Enter your first FY StartDate here
StartOfWeekNum = if List.Contains({0..6}, StartOfWeekDay) then StartOfWeekDay else 0, // Day.Sunday =0, Day.Monday =1 etc
CurrentDate = Date.From( DateTimeZone.FixedUtcNow()),
ExtendYears = if AddYears = null or AddYears =0 then Date.Year( CurrentDate ) - Date.Year( FYStartDate ) +1 else AddYears,
StartDate = Number.From( FYStartDate ),
EndDate = StartDate + ( 364 * ExtendYears ) -1,
ListDates = {StartDate..EndDate},
ConvertedToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "DateKey"}}),
DateKey = Table.TransformColumnTypes(RenamedColumns,{{"DateKey", type date}}),
DayID = Table.AddIndexColumn(DateKey, "DayID", 1, 1, Int64.Type),
YearID = Table.AddColumn(DayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1, Int64.Type),
QuarterID = Table.AddColumn(YearID, "QuarterID", each Number.RoundDown(([DayID]-1)/91)+1, Int64.Type),
MonthID = Table.AddColumn(QuarterID, "MonthID", each Number.RoundDown([DayID]/91)*3+ ( if Number.Mod([DayID],91)=0 then 0 else if Number.Mod([DayID],91)<=28 then 1 else if Number.Mod([DayID],91)<=56 then 2 else 3 ), Int64.Type),
WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
InsertYear = Table.AddColumn(WeekID, "Year", each Date.Year([DateKey]), Int64.Type),
InsertMonth = Table.AddColumn(InsertYear, "Month", each Date.Month([DateKey]), Int64.Type),
InsertDayOfMonth = Table.AddColumn(InsertMonth, "Day", each Date.Day([DateKey]), Int64.Type),
InsertMonthName = Table.AddColumn(InsertDayOfMonth, "Month Name", each Date.MonthName([DateKey]), type text),
InsertEndOfMonth = Table.AddColumn(InsertMonthName, "End of Month", each Date.EndOfMonth([DateKey]), type date),
InsertDayName = Table.AddColumn(InsertEndOfMonth, "Day Name", each Date.DayOfWeekName([DateKey]), type text),
StartOfWeek = Table.AddColumn(InsertDayName, "Start of Week", each Date.StartOfWeek([DateKey], StartOfWeekNum), type date),
EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], StartOfWeekNum), type date),
InsertYearFY = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) -1 + [YearID], Int64.Type ),
InsertPeriodFY = Table.AddColumn(InsertYearFY, "FY Period", each [MonthID] - (( [YearID] -1 ) * 13 ), Int64.Type ),
InsertWeekFY = Table.AddColumn(InsertPeriodFY, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
InsertDayOfFY = Table.AddColumn(InsertWeekFY, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type),
InsertFYPeriod = Table.AddColumn(InsertDayOfFY, "FY & Period", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Period]), 2, "0"), type text),
InsertFYWeek = Table.AddColumn(InsertFYPeriod, "FY & Week", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Week]), 2, "0"), type text),
InsertIsAfterToday = Table.AddColumn(InsertFYWeek, "IsAfterToday", each not ([DateKey] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([DateKey], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [DateKey] ), if Holidays = null then type text else type logical)
in
InsertIsHoliday
in
fnDateTableYou are fantastic. I think this will work! Thank you!
Hi Melissa,
I’ve set up the gateways for my published report, but the refresh is failing. From what I am understanding, the error includes text from this code. It is odd that this is what’s standing out and maybe something else is causing the issue, or is this a known issue?
Hi @Jackie,
Please make sure that the connection string parameters of the data sources under gateway are the same as that in the Power BI Desktop file.
I checked them and they look right. I will keep researching. Thank you!
