Measure using a slicer in a filter giving blank data

[kyleboucher]

I'm not entirely sure if it's an issue with the value from slicer, or an issue with the DAX queries I have. 

I'm trying to calculate the forecasted total for the whole year.

Now, in the data I have multiple different "forecasts".

Forecast table has these columns; Customer, Year, Measure, Forecast, MonthName, MonthNumber, Amount, Date, Forecast AUD, Forecast NZD.

The [Forecast] columns has values 0,1,2,...,11 as these are the different forecasts. Each of these numbers has values for all 12 months in the year. 

I have a slicer on the [Forecast] value.

To get the full year total for [Measure] = "Net Sales" i use the following DAX:

Forecast FY = CALCULATE(CALCULATE([Forecast Sum NZ], filter(Forecast, Forecast[Measure] = "Net Sales")), ALL('Calendar'[Ordered Month Name]))

Where [Forecast Sum NZ] is; 

Forecast Sum NZ = CALCULATE(sum(Forecast[Forecast NZD]), ALL('Calendar'[Date]))

This works fine, and ignores my month filters, and correctly changes when I change the [Forecast] slicer.

The problem:

I need this total for the "previous" [Forecast] to what is selected. Eg. slicer selected [Forecast]=1, then [Forecast Prior]=0
This is how I get the slicers value:

// slicer value
Selected Forecast Number = FIRSTNONBLANK(Forecast[Forecast],Forecast[Forecast])

// prior value
Selected Forecast Number Prior = [Selected Forecast Number] - 1

And then continue to calculate the [Forecast FY Prior];

// Calculating the prior forecast
Forecast Prior = CALCULATE( 
   CALCULATE(sum(Forecast[Forecast NZD]),
       FILTER(ALL(Forecast[Forecast]), Forecast[Forecast] = [Selected Forecast Number Prior])
   ), 
   FILTER(Forecast, Forecast[Measure] = "Net Sales")
)

// Full Year filter -- This works as intended by ignoring the month slicer
Forecast FY Prior = CALCULATE([Forecast Prior], ALL('Calendar'[Ordered Month Name]))

What I understand

When I change the slicer for [Forecast] to 1, the data has been removed for [Forecast]=0 and therefore gives me a blank value when I use the above. Even though I tried to use ALL(Forecast[Forecast]) to remove the slicer filter on this column. 

So, either my [Selected Forecast Number Prior] is incorrectly selected, or my [Forecast Prior] is invalid in it's calculations.

[Phil_Seamark · Accepted solution]

Hi @kyleboucher

One approach is to strip out the your forecast column into it's own calculated table just for the purpose of the Slicer.

You can do this easily enough using the following code :

Table = SUMMARIZECOLUMNS('Forecast'[Forecast])

But don't create a relationship between this new table and your Forecast table.

Then just change your Selected Forecase Number calculated measure as follows :

Selected Forecast Number = FIRSTNONBLANK('Table'[Forecast],'Table'[Forecast])

Let me know how you get on.

[kyleboucher]

Thank you!
This solved the problem I was having. Just had to change the [Forecast FY] initial calculation to include the new selected forecast.