topic Re: Dax help for forecast in DAX Commands and Tips

Dax help for forecast

LankaPriya — Mon, 27 Mar 2023 19:21:34 GMT

Hello,

I need help with forecast measure using power-bi Dax. from the below excel, at the end of the report, there is a constanct value 1.4, I need to use that constant value and forecast into future by subracting the last value with 1.4 and next row as previous row - 1.4 and so on until the value reaches 0.

Any help on how to get this using dax is really helpful

Year	Month	Day	WEEK ENDING DATE	waiter
2022	April	3	03/04/2022	3486
2022	April	10	10/04/2022	3358
2022	April	17	17/04/2022	3371
2022	April	24	24/04/2022	3560
2022	May	1	01/05/2022	3486
2022	May	8	08/05/2022	3404
2022	May	15	15/05/2022	3334
2022	May	22	22/05/2022	3250
2022	May	29	29/05/2022	3241
2022	June	5	05/06/2022	3442
2022	June	12	12/06/2022	3411
2022	June	19	19/06/2022	3357
2022	June	26	26/06/2022	3336
2022	July	3	03/07/2022	3351
2022	July	10	10/07/2022	3323
2022	July	17	17/07/2022	3339
2022	July	24	24/07/2022	3402
2022	July	31	31/07/2022	3479
2022	August	7	07/08/2022	3493
2022	August	14	14/08/2022	3504
2022	August	21	21/08/2022	3454
2022	August	28	28/08/2022	3523
2022	September	4	04/09/2022	3468
2022	September	11	11/09/2022	3517
2022	September	18	18/09/2022	3602
2022	September	25	25/09/2022	3668	1.4
				3666.6
				3665.2
				3663.8
				3662.4
				3661
				3659.6

Thanks

Priya

Re: Dax help for forecast

amitchandak — Tue, 28 Mar 2023 02:24:17 GMT

LankaPriya , Assume you already have a date table with future date , assuming waiter is measure or create woth sum ot max

M1= calculate(lastnonblank(Table[Date]), allselected())

m2= calculate(lastnonblankvalue(Table[Date], [waiter Measure]) , allselected())

M3 =

if(Max(Date[Date]) <[M1] , [waiter Measure], [M2] * Power(1.4, quotient(datediff([M1],Max(Date[Date]), Day),7) ))

Re: Dax help for forecast

LankaPriya — Tue, 28 Mar 2023 09:34:41 GMT

Hi Amit,

Thanks a lot for your immediate response.

I worked out the measures as below

m1 = CALCULATE(LASTNONBLANK(DATES[FULLDATE]), ALLSELECTED()) -- was getting an error at this, suspect it needs one more arugument and adjusted as below

m2 = CALCULATE(LASTNONBLANK(DATES[FULL_DATE], [waiter] ), ALLSELECTED())

m2 = CALCULATE(LASTNONBLANK(DATES[FULL_DATE], [waiter] ), ALLSELECTED()) -- this one appreared as a column in the fact table

m3 =

if (MAX(DATES[FULL_DATE]) < [m1], [m2], [waiter]*POWER(1.4,QUOTIENT(DATEDIFF([m1],max(DATES[FULL_DATE]),day),7)))

couldn't get to call [M2] here , if I use [waiter] instead, this is how it looks

I now want to subract the last value(937) - 1.4 and the resulting value(935.6) from 1.4 and so on and forecast

Thanks

Priya

Re: Dax help for forecast

LankaPriya — Tue, 28 Mar 2023 11:32:41 GMT

Hello,

I further changed the measure as below

m1 = CALCULATE(LASTNONBLANK(DATES[FULL_DATE], [waiter]), ALLSELECTED())

m2 = CALCULATE(LASTNONBLANKVALUE(DATES[FULL_DATE],[waiter]), ALLSELECTED())

m3 =

if (MAX(DATES[FULL_DATE]) < [m1], [waiter],[m2]*POWER(1.4,QUOTIENT(DATEDIFF([m1],max(DATES[FULL_DATE]),day),7)))

Below is the result I see,

How can I change m3 to get as below

18-03-2023 = 935.6

19-03-2023 = 934.2

and so on

previous value - 1.4 in each step

Thanks

Priya