Adjusted forecast based on actuals

DAX DAX Calculations
1

Hi,

I have used this video to create cumulative totals :

https://portal.enterprisedna.co/courses/313969/lectures/4857780 ,

but here in the formula at around 7min30, I would like to have the cumulative sales to add the forward forecasted months in my case ( for example actuals until August which is period 8 and add the forecasted revenue of P9 until P12).

How is it possible to do this please ?

Thanks

Regards

2

Hi @Rachid,

I took the sample file from that course and created the following measure:

Adj Forecast = 
VAR LastSale = MAXX( ALL(Sales ), Sales[OrderDate] )
VAR cumForecast =
    ADDCOLUMNS( ALLSELECTED( Dates ),
        "@Value", SWITCH( TRUE(),
            Dates[Date] <= LastSale, 
            [Total Sales],
            [2016 Forecast]
        ))
        
RETURN

SUMX( FILTER( cumForecast,
	Dates[Date] <= MAX( Dates[Date] )),
    [@Value] 
)

.

I hope this is helpful.

3

I can’t use a measure instead of the (Sales) after MAXX because my revenue is a measure.

Below is my formula that is working for Cumulative Gross Revenue Actuals

Cumulative GR Actuals =

if(isblank([GR Actuals]),

blank(),

CALCULATE( [GR Actuals], FILTER( ALLSELECTED(‘Date table’), ‘Date table’[Date] <= MAX(‘Date table’[Date]))))

4

Hi @Rachid,

You need to calculate a point in time where you switch from Actuals to Forecast.
In the course you referred to that’s the Sales[OrderDate] since that is all I have to go on at this point - it’s going to be difficult to provide better support without a sample file that matches your current situation.

5

for the Sales[OrderDate], I don’t think there will be an issue, I just need a way to insert my measure [GR Actuals] where you have put ALL(Sales)

To explain better, I don’t have a sales column in my fact table, I have only " amount" where you find all the income statements accounts, so I created a measure that filter the Revenue account.

6

image
image711×370 15 KB

ALL( Sales ) is used for calculating the LastSales date
1 - should be your Actuals measure
2 - should be your Forecast measure

I hope this is helpful

7

Hi @Rachid, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

8

Hi Melissa,

What is the @Value please ?

Thanks

9

Hi @Rachid

It is the temp column name created for the variable cumForecast.

VAR cumForecast =
    ADDCOLUMNS( ALLSELECTED( Dates ),
        **"@Value",** SWITCH( TRUE(),
            Dates[Date] <= LastSale, 
            [Total Sales],
            [2016 Forecast]
        ))

And to access the column [@Value] has been used at the last line of the code.

SUMX( FILTER( cumForecast,
	Dates[Date] <= MAX( Dates[Date] )),
    **[@Value]**
10

Hi @Rachid,

@MK3010 is correct, it’s a naming convention for temporary columns.
You can find more on that subject here.

11

Hi @Rachid, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

12

no unfortunately the answer doesn’t apply to my issue but I will try to find a way

1 Like