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 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
SUMX for Rolling Forecast with Two FactTables (Actual x Forecast)
Hi guys, I have two separeted Fact Tables (Actual and Forecast) linked in a Calendar Dimension Table. In the Forecast Table I have data of only Month of the "Year to go" (Example: Jun to Dez). Besides, in the Actual table I have only data of the YTD Months (example: Jan to May). So I need to make an weighted average using the two fact tables, filtering the lastdate with data of the Actual in the Forecast Table and interate over this virtual DAX table of Actual and Forecasting. I know that I could embend the two tables in power query. But as a matter of learning and making the worksheet more smooth. I would like to try this one. Here is my DataSet and my last Dax Measure my last result, the proble is in the subtotal over the iteration issue of sumx Thanks in !!! Data help and tips @facttables fact table and dimension table
