Applying different filters on Crosstab columns

[rksingh]

Hi,
I have a crosstab in which I am applying filters. There is a month filter which I need to apply for all the columns except one. I have a Forecast column, which should always display the yearly value, while other columns should display the monthly data (as months filter will be applied on them).

Can anyone pls let me let how to achieve it technically?
Attached is the image for better understanding of the issue. All columns marked in green needs to have month filter applied on the them and the red columns should not have month filter.

Thanks

[dax]

Hi

It is difficult to give a precise answer without knowing more about how your data is structured.  I am assuming that you don't have measures such as "Year to Date", "Full Year" that can be applied for a single period filter.  The model that I'm currently working with does this and makes it easier to have reports showing current month, year to date, full year budget, etc.

Anyway, one possible solution (and again, this is based on the assumption that your model can't support what you're trying to do directly) is to use a union - create one query based that has the filter on the individual period and create another query that has no filter on the period.  You'll need to put in dummy values e.g. a zero for those columns that aren't relevant for the second query.

Hope this is of some use,
Dax