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
| Date | Year | Month | Month Sort | Quarter | Month Year | Month Year Sort | Fiscal Month | Day | Public Holiday | Day in Week | Working Day | Working Day Number |
| 30/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Sunday | | 7 | FALSE | 1613 |
| 29/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Saturday | | 6 | FALSE | 1613 |
| 28/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Friday | | 5 | TRUE | 1613 |
| 27/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Thursday | | 4 | TRUE | 1612 |
| 26/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Wednesday | | 3 | TRUE | 1611 |
| 25/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Tuesday | Anzac Day | 2 | FALSE | 1610 |
| 24/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Monday | | 1 | TRUE | 1610 |
| 23/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Sunday | | 7 | FALSE | 1609 |
| 22/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Saturday | | 6 | FALSE | 1609 |
| 21/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Friday | | 5 | TRUE | 1609 |
| 20/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Thursday | | 4 | TRUE | 1608 |
| 19/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Wednesday | | 3 | TRUE | 1607 |
| 18/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Tuesday | | 2 | TRUE | 1606 |
| 17/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Monday | | 1 | TRUE | 1605 |
| 16/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Sunday | | 7 | FALSE | 1604 |
| 15/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Saturday | | 6 | FALSE | 1604 |
| 14/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Friday | | 5 | TRUE | 1604 |
| 13/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Thursday | | 4 | TRUE | 1603 |
| 12/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Wednesday | | 3 | TRUE | 1602 |
| 11/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Tuesday | | 2 | TRUE | 1601 |
| 10/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Monday | Easter Monday | 1 | FALSE | 1600 |
| 9/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Sunday | | 7 | FALSE | 1600 |
| 8/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Saturday | | 6 | FALSE | 1600 |
| 7/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Friday | Good Friday | 5 | FALSE | 1600 |
| 6/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Thursday | | 4 | TRUE | 1600 |
| 5/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Wednesday | | 3 | TRUE | 1599 |
| 4/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Tuesday | | 2 | TRUE | 1598 |
| 3/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Monday | | 1 | TRUE | 1597 |
| 2/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Sunday | | 7 | FALSE | 1596 |
| 1/04/2023 | 2023 | April | 4 | Q2 | Apr-23 | 202304 | April | Saturday | | 6 | FALSE | 1596 |
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,185 | 16/05/2023 |
| 1,501,997 | 15/05/2023 |
| | 14/05/2023 |
| | 13/05/2023 |
| 503,046 | 12/05/2023 |
| 710,161 | 11/05/2023 |
| 1,005,275 | 10/05/2023 |
| 1,398,928 | 9/05/2023 |
| 906,827 | 8/05/2023 |
| | 7/05/2023 |
| | 6/05/2023 |
| 993,050 | 5/05/2023 |
| 579,540 | 4/05/2023 |
| 1,003,600 | 3/05/2023 |
| 867,650 | 2/05/2023 |
| 667,717 | 1/05/2023 |
| | 30/04/2023 |
| | 29/04/2023 |
| 1,308,653 | 28/04/2023 |
| 553,636 | 27/04/2023 |
| 959,788 | 26/04/2023 |
| | 25/04/2023 |
| 782,730 | 24/04/2023 |
| | 23/04/2023 |
| | 22/04/2023 |
| 1,235,900 | 21/04/2023 |
| 622,685 | 20/04/2023 |
| 1,216,950 | 19/04/2023 |
| 1,730,130 | 18/04/2023 |
| 803,400 | 17/04/2023 |
| | 16/04/2023 |
| | 15/04/2023 |
| 502,146 | 14/04/2023 |
| 1,035,386 | 13/04/2023 |
| 1,115,310 | 12/04/2023 |
| 859,685 | 11/04/2023 |
| | 10/04/2023 |
| | 9/04/2023 |
| | 8/04/2023 |
| | 7/04/2023 |
| 578,061 | 6/04/2023 |
| 1,167,161 | 5/04/2023 |
| 578,425 | 4/04/2023 |
| 2,126,646 | 3/04/2023 |
| | 2/04/2023 |
| 3,049,423 | 1/04/2023 |
