How to keep the slicer filter but override the rest of the context?

[Anonymous]

I'm creating a line and stacked column chart and having a problem with the measure that should be plotted on the line. The x-axis is time (one entry for each month). I have two slicers, and teh bars on the chart are correct.

We want the line to be a constant valuer that indicates the budget as it was planned in January. The budget is updated every month with an evergreen value, but we want to display the original budget as a constant for each monthly entry. So I want to continue using the slicer values in the filter but want to override the time context. I tried:

Annual Budget = CALCULATE (
                    'Measures'[Annual OP],
                    KEEPFILTERS(Month('Date'[Date] = 1))
                 )

What I'm trying to do here is keep the slicer filters but then force date filter to always use month 1. What I'm seeing though is that the Annual Budget still cahnges monthly and equals the Annual OP value for each month (instead of always using the Annual OP value for January).

[littlemojopuppy]

Try this...

Annual Budget = CALCULATE (
                    'Measures'[Annual OP],
                    ALLEXCEPT('Date','Date'[Date])
                 )

[Anonymous]

Thank you for the reply. I think this is close, but I'm still having problems because I didn't describe the slicers well enough. 

I have slicers on 2 of the dimensions

• Date
• Organization

The first slicer is on the Year field in the date hierarchy in the Date dimension.

The second slicer is on the Organization hierarchy in the Organization dimension.

I need the first slicer to continue restricting the graph for a single year.

The problem is that the month field that I'm iterating over and want to keep constant (to retrieve January) is in the same date hierarchy as the Year field in the slicer.

[littlemojopuppy · Accepted solution]

Apologies...I missed something the first time through it.  You want to ALWAYS use January numbers.  What I originally posted won't do that.  Try this instead.

Annual Budget = 
	DIM	CurrentYear = YEAR(TODAY())
	RETURN

	CALCULATE(
		[Annual OP],
		FILTER(
			DateTable,
			DateTable[Year] = CurrentYear &&
			DateTable[MonthNumber] = 1
		),
		ALLEXCEPT(
			FactTable,
			Organization[ID]
		)
	)

The FILTER part should keep it for January for the current year.  And ALLEXCEPT should keep the organization filters.  I have no idea what any of your tables/fields are named so I improvised...

[Anonymous]

I didn't think of using a variable, so let me play with that. This was very helpful.

I'm still not getting what I want, but I'm not sure if some of it comes from misunderstanding the data.