Protect a measure from slicers and filters (Year-to-go calculation)

[Anonymous]

Hi,

I am trying to calculate the difference between my year-to-date sales and the forecasted sales at the end of year.

Whenever I use this measure in december I have the correct value (the difference between my forecasted sales and my sales at the end of year.

Forecast End of Year =

VAR FORECAST_END_OF_YEAR =

CALCULATE( [YTD Sales (BU)];

     FILTER('BU';'BU'[Version] = "BUX1");

     FILTER(Date;Date[Month] = "Dec" ))

RETURN FORECAST_END_OF_YEAR  -  [YTD Sales N (USD)] 

This indicator is displayed on a monthly dashboard which use filter of course .

The problem is that the variable FORECAST_END_OF_YEAR is filtered any time a slicer is applied to the measure. Therefore the Forecast's value is 0 since it is basically calculated only for December.

Is there a mean to prevent any modifications of FORECAST_END_OF_YEAR value by filter or slicer?

Or maybe do you have some tips to perform my calculation of the indicator otherwise? 🙂

I am considering taking advantage of the MAX function in order to ensure that I have always my forecast at the end of the year (there is thread about this on the community).

Thanks for your help.

Regards

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Just see from your description here, using some ALLs could be helpful. Please try it out.

Forecast End of Year =
VAR FORECAST_END_OF_YEAR =
    CALCULATE (
        [YTD Sales (BU)];
        FILTER ( ALL ( 'BU' ); 'BU'[Version] = "BUX1" );
        FILTER ( ALL ( Date ); Date[Month] = "Dec" );
        ALL ( othertables )
    )
RETURN
    FORECAST_END_OF_YEAR - [YTD Sales N (USD)]

Best Regards,

[Anonymous]

Dear @v-jiascu-msft,

I miss something to perform my calculations.

In fact, I need to avoid filtering only on the "Date" and "BU" data.

While other dimensions, such as cities or products should be dynamic.

It seems that the function ALLEXCEPT could help me, but it does not work for the moment.
Such as :

 Forecast End of Year =
VAR FORECAST_END_OF_YEAR =
    CALCULATE (
        [YTD Sales (BU)];
        FILTER ( ALL ( 'BU' ); 'BU'[Version] = "BUX1" );
        FILTER ( ALL ( Date ); Date[Month] = "Dec" );
        ALLEXCEPT ( 'Sales'; Sales[Brands] ) )
    )
RETURN
    FORECAST_END_OF_YEAR - [YTD Sales N (USD)]

Thanks again for your help.

Regards,

Benjamin

[v-jiascu-msft]

Hi Benjamin,

If you still want other dimensions to work, don't wrap them in ALL. It could be like this.

 Forecast End of Year =
VAR FORECAST_END_OF_YEAR =
    CALCULATE (
        [YTD Sales (BU)];
        FILTER ( ALL ( 'BU' ); 'BU'[Version] = "BUX1" );
        FILTER ( ALL ( Date ); Date[Month] = "Dec" );
        ALLEXCEPT ( 'Sales'; Sales[Brands] ) )  //delete this line.
    )
RETURN
    FORECAST_END_OF_YEAR - [YTD Sales N (USD)]

Best Regards,

[Anonymous]

Hi @v-jiascu-msft,

Obviously... The purpose of ALL function in this case is to state which dimensions must not be modified, my bad.
But somehow it does not work on my report. If I select a product, FORECAST_END_OF_YEAR is not filtered contrary to [YTD Sales N (USD)].

FORECAST_END_OF_YEAR is not sensitive to slicers and page filters applied to other dimensions than Date & BU.

I will update this topic as soon as I get the solution.

Thanks for your help.

Benjamin

[v-jiascu-msft]

Hi Benjamin,

That could be caused by the improper relationship. Can you share a dummy sample? Please mask the sensitive parts first.

Best Regards,

[Anonymous]

@v-jiascu-msft,

What do you mean by improper relationship ? When I test the relationships of  budgeted or actual sales with my dimensions, it works perfectly.

Sorry I can't share an example since the file is live connected to a database. Database that I cannot see by the way ! I will investigate...

Thanks for your support.

Benjamin

[Anonymous]

Hi @v-jiascu-msft,

It works perfectly, this is exactly what I was looking for ! Thanks a lot for your input, ALL is a very interesting function.

Regards,

Benjamin