Forum Discussion

Anneka's avatar
Anneka
New Member
3 years ago

Using yesterday's rolling average as forecast for all future working days only DAX

Hi there,

 

I want to forecast investor deposits, using the prior 30 Working Day's Rolling Average from YESTERDAY for all future dates. I only want the forecast to show on 'working' days and I only want the forecast to show for future dates, not for past dates.

 

E.g. if the rolling average for yesterday was $900,000, I want $900,000 to show as the forecast for all working days in the future, only. The next day the rolling average might change to $950,000 and then I would want that as the forecast.

 

This is my measure for the rolling average

Rolling 20WD Avg (Deposits) =
VAR NumberofDays = 20
VAR MaxWorkingDay = Max('Date'[Working Day Number])
VAR MinWorkingDay = MaxWorkingDay - NumberofDays
VAR MaxDate = MAX ('Date'[Date] )
VAR DatesToUse =
    FILTER(
        ALL ( 'Date' ),
        'Date'[Working Day Number] >= MinWorkingDay &&
        'Date'[Date] <= MaxDate
    )
VAR Result =
        DIVIDE (CALCULATE( [Deposits], DatesToUse ),
        (NumberofDays + 1) )
Return
Result
 
Then I made a measure trying to return the rolling average for yesterday only
Rolling 20WD Avg (Deposits) (Yesterday) =
CALCULATE([Rolling 20WD Avg (Deposits)], FILTER('Date','Date'[Working Day] -1 ))
 
Then with this measure I am attempting to forecast only for working days, 
Forecast Deposits =
        CALCULATE( [Rolling 20WD Avg (Deposits) (Yesterday)] ,
        FILTER('Date','Date'[Working Day] )
        )
however it causes my visual to error, saying "Couldn't load the data for this visual", calculation error in measure 'Table'[Rolling 20WD Avg (Deposits) (Yesterday): Cannot convert value 'True' of type Text to type Number.
 
I seem to either be able to get yesterday's working average to show as the forecast for all future days (i.e. weekends and workdays), or the rolling average changing by day into the future, for workdays only, but not both at once. I also can't figure out how to get the forecast to show for only future dates. I don't think I can use Max of Deposit date because not all days have deposits, so if yesterday was blank, I want it to stay blank and not show a forecast until today.
 
I have spent days looking through the community and resources online and am totally stuck, so any help would be greatly appreciated! I've included sample data below:
 
Sample Date Table
DateYearMonthMonth SortQuarterMonth YearMonth Year SortFiscal MonthDayPublic HolidayDay in WeekWorking DayWorking Day Number
30/04/20232023April4Q2Apr-23202304AprilSunday 7FALSE1613
29/04/20232023April4Q2Apr-23202304AprilSaturday 6FALSE1613
28/04/20232023April4Q2Apr-23202304AprilFriday 5TRUE1613
27/04/20232023April4Q2Apr-23202304AprilThursday 4TRUE1612
26/04/20232023April4Q2Apr-23202304AprilWednesday 3TRUE1611
25/04/20232023April4Q2Apr-23202304AprilTuesdayAnzac Day2FALSE1610
24/04/20232023April4Q2Apr-23202304AprilMonday 1TRUE1610
23/04/20232023April4Q2Apr-23202304AprilSunday 7FALSE1609
22/04/20232023April4Q2Apr-23202304AprilSaturday 6FALSE1609
21/04/20232023April4Q2Apr-23202304AprilFriday 5TRUE1609
20/04/20232023April4Q2Apr-23202304AprilThursday 4TRUE1608
19/04/20232023April4Q2Apr-23202304AprilWednesday 3TRUE1607
18/04/20232023April4Q2Apr-23202304AprilTuesday 2TRUE1606
17/04/20232023April4Q2Apr-23202304AprilMonday 1TRUE1605
16/04/20232023April4Q2Apr-23202304AprilSunday 7FALSE1604
15/04/20232023April4Q2Apr-23202304AprilSaturday 6FALSE1604
14/04/20232023April4Q2Apr-23202304AprilFriday 5TRUE1604
13/04/20232023April4Q2Apr-23202304AprilThursday 4TRUE1603
12/04/20232023April4Q2Apr-23202304AprilWednesday 3TRUE1602
11/04/20232023April4Q2Apr-23202304AprilTuesday 2TRUE1601
10/04/20232023April4Q2Apr-23202304AprilMondayEaster Monday1FALSE1600
9/04/20232023April4Q2Apr-23202304AprilSunday 7FALSE1600
8/04/20232023April4Q2Apr-23202304AprilSaturday 6FALSE1600
7/04/20232023April4Q2Apr-23202304AprilFridayGood Friday5FALSE1600
6/04/20232023April4Q2Apr-23202304AprilThursday 4TRUE1600
5/04/20232023April4Q2Apr-23202304AprilWednesday 3TRUE1599
4/04/20232023April4Q2Apr-23202304AprilTuesday 2TRUE1598
3/04/20232023April4Q2Apr-23202304AprilMonday 1TRUE1597
2/04/20232023April4Q2Apr-23202304AprilSunday 7FALSE1596
1/04/20232023April4Q2Apr-23202304AprilSaturday 6FALSE1596
 
Sample Deposit Data
 Deposits Date
  31/05/2023
  30/05/2023
  29/05/2023
  28/05/2023
  27/05/2023
  26/05/2023
  25/05/2023
  24/05/2023
  23/05/2023
  22/05/2023
  21/05/2023
  20/05/2023
  19/05/2023
  18/05/2023
  17/05/2023
                   533,18516/05/2023
               1,501,99715/05/2023
  14/05/2023
  13/05/2023
                   503,04612/05/2023
                   710,16111/05/2023
               1,005,27510/05/2023
               1,398,9289/05/2023
                   906,8278/05/2023
  7/05/2023
  6/05/2023
                   993,0505/05/2023
                   579,5404/05/2023
               1,003,6003/05/2023
                   867,6502/05/2023
                   667,7171/05/2023
  30/04/2023
  29/04/2023
               1,308,65328/04/2023
                   553,63627/04/2023
                   959,78826/04/2023
  25/04/2023
                   782,73024/04/2023
  23/04/2023
  22/04/2023
               1,235,90021/04/2023
                   622,68520/04/2023
               1,216,95019/04/2023
               1,730,13018/04/2023
                   803,40017/04/2023
  16/04/2023
  15/04/2023
                   502,14614/04/2023
               1,035,38613/04/2023
               1,115,31012/04/2023
                   859,68511/04/2023
  10/04/2023
  9/04/2023
  8/04/2023
  7/04/2023
                   578,0616/04/2023
               1,167,1615/04/2023
                   578,4254/04/2023
               2,126,6463/04/2023
  2/04/2023
               3,049,4231/04/2023
DAX
Forecasting
RollingForecast

1 Reply

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anneka , check part "FILTER('Date','Date'[Working Day] " you need second argument like 'Date'[Working Day]  = TRUE / FALSE...