Filter 3 latest months with one month value

[kingtoni]

I have a report in Excel with month filter based on my powerpivot data model. I'd like to show 3 latest months on my report all the time.
- When user filters report with month 112015, I'd like to show months 112015, 102015 and 092015
- When user filters report with month 022015, I'd like to show months 022015, 012015 and 122014

I tried with top 3 values in my filter but it doesn't work when user wants to change month.

What kind of formula I need to create in my powerpivot to get this to work?

 

[Gordonik]

You can try with a below measure:

CALCULATE (
[Your measure],
FILTER (
ALL ( 'Date'[PERIOD NO] ),
'Date'[PERIOD NO] >= MAX( 'Date'[PERIOD NO] ) -3
)
)

But before this you should do a mapping (can be in related table 'DATE')

'DATE' table
DATE PERIOD NO
012014 1
022014 2
...
122014 12
012015 13
...

Alternatively you can group your months by quarters and allow user to filter by quarter.
Lets say, filtering Q1 will show 01,02,03 months in a pivot

 

[kingtoni]

I've tried to use your formula but it doesn't work for me. I have a date table in my model and I have linked these tables. I created max-measure for month and use that in my filter. I can use filter as range and it works. Only thing is that I have to set two months in my filter and I can set what ever range I want. Also filter have figure like 102099 because it contains numbers from min and max month

Grouping is not the answer because I want to view any month and -3.

 

[Gordonik]

We do not know what you have because you did not share it. How is your data model look like?
That was just example with a logic. Should be customised to work with your model.

 

[kingtoni]

I have two tables in my data model. Fact table and calendar table. I have sales data per month in fact table. I have made a calculated column for data and made a relationship with that column.

My problem with you example formula is that I cannot reduce -3 from my month. I have to do that from my data field. What happens when I do the following 201501 -3 = 201498? That is not a month in my data.

I understand your formula but I don't YET know how to get that to work in my data model.


How about I make max and min measures for month and calculate difference? Is there any idea?

 

[kingtoni]

I didn't see PERIOD NO in your first post. That is a good idea! I'll try that next.

 

[Gordonik]

[TABLE="width: 236"]
<tbody>[TR]
[TD]'DATE' table
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] DATE[/TD]
[TD="align: right"] PERIOD NO
[/TD]
[/TR]
[TR]
[TD="align: right"]012014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]022014[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] ...[/TD]
[TD] …[/TD]
[/TR]
[TR]
[TD="align: right"]122014[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]012015[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD] ...[/TD]
[TD] …[/TD]
[/TR]
</tbody>[/TABLE]

Sorry, I did not paste it properly. Maybe now it is more readable