Forum Discussion

jayjay0306's avatar
jayjay0306
Helper III
6 years ago

Measure overruling filter

Hi people,

I need you help:

I have a sales forecast report with several forecast versions, one for each month.

The report shows the sales according to the latest forecast, therefore the report has a pagefilter: forecast version="latest forecast".

now, I need to make a measure which shows the salesforecast for previous month (forecast version="previous forecast").

BUT, when I have a pagefilter with forecast version="latest forecast", the salesforecast for previous month turns out empty (off course it is filtered out).

 

Therefore I need to make a measure which "overrules" the pagerfilter and then filter: forecast version="previous forecast".

 

example:

 

I have 3 accounts with 2 forecast versions: forecast 5 (=previous version) and forecast 6 (latest forecast):

My pagefilter is set to latest forecast version (=max(forecast version).

 

I have tried to make a measure using the ALL()-function in order to overrule the forecast-filter, and then filter on the previous forecastversion:

Sales Forecast (Previous version)= 
VAR PrevForecastVersion =CALCULATE (
        MAX ('Forecast'[Version]);
        FILTER (
            ALL ('Forecast'[Version]);
            'Forecast'[Version]= MAX('Forecast'[Version])-1 )
        )
RETURN
    CALCULATE (
        SUM( ‘Actual’[Sales]);
        FILTER (ALL(‘Forecast'); 'Forecast'[Version]= PrevForecastVersion)
    )

This gives me the Sales forecast for "previous forecast version", but only as total for all accounts:

 

Question: How do I make the measure, so I gives me the sale on the previous Forecast version, but keeps the implicit filter (i.e. by account)?

 

All help is greatly appreciated. 🙂

 

Br,

Jakob

 

Need Help
Tips and Tricks

3 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi jayjay0306 ,

     

    You may create measure like DAX below.

     

    Sales Forecast (Previous version) =
VAR PrevForecastVersion =
    CALCULATE (
        MAX ( 'Forecast'[Version] ),
        FILTER (
            ALL ( 'Forecast'[Version] ),
            'Forecast'[account] = MAX ( 'Forecast'[account] )
                && 'Forecast'[Version]
                    = MAX ( 'Forecast'[Version] ) - 1
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Actual'[Sales] ),
        FILTER (
            ALL ( 'Forecast' ),
            'Forecast'[account] = MAX ( 'Forecast'[account] )
                && 'Forecast'[Version] = PrevForecastVersion
        )
    )

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jayjay0306's avatar
      jayjay0306
      Helper III
      6 years ago

      Hi Amy,

      Brilliant! It works. I'm almost there.

      The only issue with your calculation is the "total", which doesn't show the total, but value in account "1".

      Is it possible to make it show the "Total" in the buttom?

      thanks.

      Br,

      Jayjay0306