topic Forcasting Last 90 Days in DAX Commands and Tips

Forcasting Last 90 Days

Bryanna — Thu, 30 Mar 2023 18:10:04 GMT

Hi,

We are trying to forecast forward how many cases we will ship based on the Past 90 days excluding weekends and applying a weighted percentage increase based on what time of the month we are in. The first third is a 1.0 multiplier second 1.05, Third 1.12.

Below is a screenshot of the table and the output along with the formula that we are using. Currently the issue is its only looking at the the specific date for the past 90 days but its not accounting for the weekend.

Volume Forecast =

VAR

MonthlyLY = Calculate([Customer Cases Shipped],DATEADD('Calendar Table'[Date],-1,MONTH))

VAR

MONTHLY2 = CALCULATE([Customer Cases Shipped],DATEADD('Calendar Table'[Date], -2,MONTH))

VAR

MONTHLY3 = CALCULATE([Customer Cases Shipped],DATEADD('Calendar Table'[Date], -3,MONTH))

RETURN

DIVIDE(MonthlyLY + MONTHLY2 + MONTHLY3 , 3, 0)

Re: Forcasting Last 90 Days

lbendlin — Mon, 03 Apr 2023 00:31:52 GMT

excluding weekends and applying a weighted percentage increase based on what time of the month we are in.

That is guaranteed to end in tears and frustration. Weeks and months are not compatible. Please explain what a third is for a month like August with 31 days.

And you haven't even mentioned holidays yet.

Re: Forcasting Last 90 Days

Bryanna — Mon, 03 Apr 2023 14:18:47 GMT

Hi,

The months are broken down by date so < 11, 1 < 22 ,2 and > 22, is 3..if we could take holidays into account as well that would be great but it wasnt brought up in the initial ask.

So youre saying the best way to do a forcast is month to month and not week to week?

Re: Forcasting Last 90 Days

lbendlin — Tue, 04 Apr 2023 00:24:37 GMT

no, I would rather do it by working days.