Forum Discussion

AZJohnPowerBI's avatar
AZJohnPowerBI
Helper I
1 year ago

Forecast Line Graph

Here's a picture of the line graph

 

 

When I drill up to the month view, the forecasted section (the red on the right) disappears, and only shows the appointments scheduled from Jan 2023-Oct2024.

 

Here are the measures I used. For the slicer:

I made a table called 'Opportunity Growth Rate' ( from DataCamp, [https://www.datacamp.com/tutorial/advanced-analytical-features-in-power-bi-tutorial]). I used a what-if parameter, and set the minimum to -1, and maximum 1, [

Opportunity Growth Rate = GENERATESERIES(-1, 1, 0.01)
Which gave me the Opportunity Growth Value. So my slicer was set.
 
Then I used COUNT('Fact Epic'[Appts Made]) for my main additive that I wanted forecasted.
Model: Star Schema so the Date table is separate.
Then I made two different measures for a forecast. This one starts the forecast at the end of October, and the other
I use TODAY() instead of >date(2024,10,31)
Jan2023-Oct2024_Forecast = if(
               MAX('DIM Date'[Date]) > date(2024,10,31),
               CALCULATE(SUM('Fact Epic'[Appts Made]),
               DATEADD('DIM Date'[Date],-1,YEAR)) *
               (1+'Opportunity Growth Rate'[Opportunity Growth Rate Value]),
               SUM('Fact Epic'[Appts Made])
           )
 

]

For my rolling total:

#Appt_RollingTotal =
    VAR MAXDate = MAX('DIM Date'[Date])
    RETURN
    CALCULATE(
        COUNT('Fact Epic'[Appts Made]),
        REMOVEFILTERS('DIM Date'),
        'DIM Date'[Date] <= MAXDate
    )
 
For my past_date measure:
# Appts_PD =
    CALCULATE(
        COUNT('Fact Epic'[Appts Made]),
        DATEADD('DIM Date'[Date],-1,DAY)
    )
 
and the date_filter, so I can see what days are being used:
Date_Filter = COMBINEVALUES(
                " -- ", MIN('DIM Date'[Date]),MAX('DIM Date'[Date]),COUNTROWS('DIM Date')
                )
 
Originally, I use Date_Filter instead of the #Appts_PD so it would show me which days it was calculating into the forecast.
I got this idea from the youtube video: [https://www.youtube.com/watch?v=I8Y5fBGwqeQ]
 
The problem I'm facing however is that the forecast doesn't work if I drill up to the month-view. It is too hard to read with data labels however if I set it to the date.
 
Also, I'm confused on how to make a previous_date measure. The one I have measures only the previous day or month, but not all of them.
So I did not use it:
Previous_Month =
    CALCULATE(
        [ApptsMade],
        MONTH ( 'DIM Date'[Date] ) = MONTH( TODAY()) -1
    ).
 
Any help would be greatly appreciated! Am I supposed to use previous year? previous month? How can I see the forecast at the monthly view?
 
Need Help
Tutorial Requests

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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.

    • AZJohnPowerBI's avatar
      AZJohnPowerBI
      Helper I
      1 year ago

      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 =

      Jan2023-Oct2024_Forecast = if(
                     MAX('DIM Date'[Date]) > date(2024,10,31),
                     CALCULATE(SUM('Fact Epic'[Appts Made]),
                     DATEADD('DIM Date'[Date],-1,YEAR)) *
                     (1+'Opportunity Growth Rate'[Opportunity Growth Rate Value]),
                     SUM('Fact Epic'[Appts Made])
                 )
       
      The Date table I referenced, I used the following in the advanced editor:

      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

    • AZJohnPowerBI's avatar
      AZJohnPowerBI
      Helper I
      1 year ago

      I wanted to follow up with this thread, were you able to find a solution?

      Do you know how to have Power BI show the Monthly view?