Include unfiltered dimension in measure

[Dennis_Pedersen]

Hi 

Basically I have 1 dimension table(date) and 2 fact tables (sales and budget).

Now if I have a page filter saying year = 2017 and 2018. I also need budget for 2019 (but not sales)

So I need to ignore the filter, which maybe done with ALL, but I would like year as column also, and with 2019 included.

For 2017 and 2018 both sales and budget is shown, but for 2019 only budget

Is it even possible?

[amitkar1980]

Assuming we can use a condition like "sales amount to be only shown for years which are earlier to the latest year in the selected list", something like this may work :

BudAmount = sum(Budget[BudgetAmount])

Maxyear = calculate(max(DateYear[Year]),ALL(Sales))

SalesAmt = calculate(sum(Sales[SalesAmount]),FILTER(Sales,Sales[Year]< [Maxyear]))

[Dennis_Pedersen]

Thanks amitkar1980.

In this case you are making filter on SalesAmt. I was thinking about making filter on BudgetAmount.

Because if I want the user to be able to change the report filter date, for exampel from 01.01.2017-31.08.2018.

Then my wish that the budget for all of 2019 could also be shown and also with the column header 2019.

But I can't see a solution for that, and maybe not possible?

[amitkar1980]

I guess, it will be easier if you select the full set of dates in your report filter but then only show the sales amount for the years prior to the max year in the selected values in the report filter. 

E.g. if you want to show 2017,2018 data for sales but 2017,2018,2019 data for budget,  then select all 3 years in the report filter

Because, if you want to do the other way round, it may be quite difficult as the report filters apply a overriding context to the entire dataset for the particular report in terms of what you can show on rows and columns.

Also assuming that you want to show the years on columns and measure names on rows , you will need to create a Measures table with the required measure names and then map the values against the measure names to the real calculated measures

The calcs in this case would be :

SalesAmt = var maxyear=calculate(max(DateYear[year]),ALLSELECTED(DateYear[year]),all(Sales[SalesYear])) return calculate(sum(Sales[SalesAmount]),CALCULATETABLE(values(Sales[SalesYear]), Sales[SalesYear]< maxyear))

BudAmount = sum(Budget[BudgetAmount])

MeasureValue = if(SELECTEDVALUE(MeasureName[MeasureName])="Budget Amount",[BudAmount],[SalesAmt])

Then you can have a matrix with year on the