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

Anneka — Fri, 19 May 2023 00:16:25 GMT

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

Apr-23

202304

April

Sunday

FALSE

1613

29/04/2023

2023

April

Apr-23

202304

April

Saturday

FALSE

1613

28/04/2023

2023

April

Apr-23

202304

April

Friday

TRUE

1613

27/04/2023

2023

April

Apr-23

202304

April

Thursday

TRUE

1612

26/04/2023

2023

April

Apr-23

202304

April

Wednesday

TRUE

1611

25/04/2023

2023

April

Apr-23

202304

April

Tuesday

Anzac Day

FALSE

1610

24/04/2023

2023

April

Apr-23

202304

April

Monday

TRUE

1610

23/04/2023

2023

April

Apr-23

202304

April

Sunday

FALSE

1609

22/04/2023

2023

April

Apr-23

202304

April

Saturday

FALSE

1609

21/04/2023

2023

April

Apr-23

202304

April

Friday

TRUE

1609

20/04/2023

2023

April

Apr-23

202304

April

Thursday

TRUE

1608

19/04/2023

2023

April

Apr-23

202304

April

Wednesday

TRUE

1607

18/04/2023

2023

April

Apr-23

202304

April

Tuesday

TRUE

1606

17/04/2023

2023

April

Apr-23

202304

April

Monday

TRUE

1605

16/04/2023

2023

April

Apr-23

202304

April

Sunday

FALSE

1604

15/04/2023

2023

April

Apr-23

202304

April

Saturday

FALSE

1604

14/04/2023

2023

April

Apr-23

202304

April

Friday

TRUE

1604

13/04/2023

2023

April

Apr-23

202304

April

Thursday

TRUE

1603

12/04/2023

2023

April

Apr-23

202304

April

Wednesday

TRUE

1602

11/04/2023

2023

April

Apr-23

202304

April

Tuesday

TRUE

1601

10/04/2023

2023

April

Apr-23

202304

April

Monday

Easter Monday

FALSE

1600

9/04/2023

2023

April

Apr-23

202304

April

Sunday

FALSE

1600

8/04/2023

2023

April

Apr-23

202304

April

Saturday

FALSE

1600

7/04/2023

2023

April

Apr-23

202304

April

Friday

Good Friday

FALSE

1600

6/04/2023

2023

April

Apr-23

202304

April

Thursday

TRUE

1600

5/04/2023

2023

April

Apr-23

202304

April

Wednesday

TRUE

1599

4/04/2023

2023

April

Apr-23

202304

April

Tuesday

TRUE

1598

3/04/2023

2023

April

Apr-23

202304

April

Monday

TRUE

1597

2/04/2023

2023

April

Apr-23

202304

April

Sunday

FALSE

1596

1/04/2023

2023

April

Apr-23

202304

April

Saturday

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

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

some_bih — Fri, 19 May 2023 06:34:35 GMT

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

topic Re: Using yesterday's rolling average as forecast for all future working days only DAX in DAX Commands and Tips

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

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