Forum Discussion
Forecast Line Graph
AZJohnPowerBI So, how is the visual configured exactly? Are you using an auto time intelligence date hierarchy? Generally if you would build your own hierarchy you wouldn't be able to to do the forecasting because your x-axis would be categorical instead of continuous which is quite likely in some way related to your issue.
Greg! How cool is that, love your book on M Code. I'm working through it right now.
I have the auto time-intelligence feature off, but oddly enough, it seems Power BI made a hierarchy from the one I made. I'm not sure what her name is that wrote the M Code to the Date table function but it is awesome. I'll attach it to the bottom here.
The Measures used:
ApptsMade = SUM('Fact Epic'[Appts Made])
Jan2023-Oct2024 =
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7 then [Year] else [Year] -1 ),
InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),
InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7 then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),
InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),
InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),
InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),
InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,
InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
if [CurrentThursday] < [ISOWeekJan4]
then Date.AddDays([CurrentThursday],-3)
else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
,type date),
InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10) + [DayInWeek]),3)),
InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),
fnPeriod445a = (weekNum) => let
Periods =
{
{(x)=>x<5, [P=1,Q=1]},
{(x)=>x<9, [P=2,Q=1]},
{(x)=>x<14, [P=3,Q=1]},
{(x)=>x<18, [P=4,Q=2]},
{(x)=>x<22, [P=5,Q=2]},
{(x)=>x<27, [P=6,Q=2]},
{(x)=>x<31, [P=7,Q=3]},
{(x)=>x<35, [P=8,Q=3]},
{(x)=>x<40, [P=9,Q=3]},
{(x)=>x<44, [P=10,Q=4]},
{(x)=>x<48, [P=11,Q=4]},
{(x)=>true, [P=12,Q=4]}
},
Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
in
Result,
InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
in
RemovedColumns
in
CreateDateTable