cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cthurston Helper II
Helper II

Using a filter to show selected month and next month

Next Month.PNG

 

 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
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])

t1.PNG

 

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. Smiley Happy

 

 

r4.PNG

 

Regards

View solution in original post

4 REPLIES 4
Super User IV
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!

cthurston Helper II
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
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])

t1.PNG

 

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. Smiley Happy

 

 

r4.PNG

 

Regards

View solution in original post

cthurston Helper II
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. 

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors