cancel
Showing results for
Did you mean:
Helper II

## Using a filter to show selected month and next month

I am attempting to use the filter such that the filter selection represents the current month and next month will represent the month after.  Any suggestions?  I have looked through the DAX functions with no ideas on how to accomplish this.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Using a filter to show selected month and next month

Hi @cthurston,

If I understand you correctly, you should be able to follow steps below to get your expected result.

1. Create an individual Calendar table if you don't have one yet. And make sure there is no relationship between your fact table and the Calendar table.

`CalendarTable = CALENDARAUTO()`
`Year = YEAR(CalendarTable[Date])`
`Month = MONTH(CalendarTable[Date])`

2. Use the formula below to create two measures to indicate if the date from fact table is current selected month or next month.

```IsCurrentMonth =
var aa= MAX ( CalendarTable[Year] )
return
IF (
YEAR ( MAX ( Sales[Date] ) ) = MAX ( CalendarTable[Year] )
&& MONTH ( MAX ( Sales[Date] ) ) = MAX ( CalendarTable[Month] ),
1,
0
)
```
```IsNextMonth =
VAR currentSelectedMonth =
MAX ( CalendarTable[Month] )
VAR currentSelectedYear =
MAX ( CalendarTable[Year] )
VAR NextMonthSelected =
IF ( currentSelectedMonth = 12, 1, currentSelectedMonth + 1 )
VAR nextYearSelected =
IF ( currentSelectedMonth = 12, currentSelectedYear + 1, currentSelectedYear )
RETURN
IF (
YEAR ( MAX ( Sales[Date] ) ) = nextYearSelected
&& MONTH ( MAX ( Sales[Date] ) ) = NextMonthSelected,
1,
0
)
```

Note: Replace "Sales" your fact table name.

3. Use the measures as visual level filter(IsNextMonth is 1) to get the corresponding result.

Regards

4 REPLIES 4
Super User IV

## Re: Using a filter to show selected month and next month

In general, you create a custom column along the lines of:

`IsCurrentMonth = IF(MONTH(TODAY())=MONTH([Date]),1,0)`

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helper II

## Re: Using a filter to show selected month and next month

That is the current solution I am using to show current month.  My intention is to show the selected filter month and have the adjacent table show the next relevant to the selected month.  I knew my trouble would be describing exactly what I'm trying to do, does this clarify it any?

Microsoft

## Re: Using a filter to show selected month and next month

Hi @cthurston,

If I understand you correctly, you should be able to follow steps below to get your expected result.

1. Create an individual Calendar table if you don't have one yet. And make sure there is no relationship between your fact table and the Calendar table.

`CalendarTable = CALENDARAUTO()`
`Year = YEAR(CalendarTable[Date])`
`Month = MONTH(CalendarTable[Date])`

2. Use the formula below to create two measures to indicate if the date from fact table is current selected month or next month.

```IsCurrentMonth =
var aa= MAX ( CalendarTable[Year] )
return
IF (
YEAR ( MAX ( Sales[Date] ) ) = MAX ( CalendarTable[Year] )
&& MONTH ( MAX ( Sales[Date] ) ) = MAX ( CalendarTable[Month] ),
1,
0
)
```
```IsNextMonth =
VAR currentSelectedMonth =
MAX ( CalendarTable[Month] )
VAR currentSelectedYear =
MAX ( CalendarTable[Year] )
VAR NextMonthSelected =
IF ( currentSelectedMonth = 12, 1, currentSelectedMonth + 1 )
VAR nextYearSelected =
IF ( currentSelectedMonth = 12, currentSelectedYear + 1, currentSelectedYear )
RETURN
IF (
YEAR ( MAX ( Sales[Date] ) ) = nextYearSelected
&& MONTH ( MAX ( Sales[Date] ) ) = NextMonthSelected,
1,
0
)
```

Note: Replace "Sales" your fact table name.

3. Use the measures as visual level filter(IsNextMonth is 1) to get the corresponding result.

Regards

Helper II

## Re: Using a filter to show selected month and next month

This did it thank you!  You also opened my world as I was unaware we could use variable within BI in this way.

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors