Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cthurston
Advocate II
Advocate 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
v-ljerr-msft
Employee
Employee

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

5 REPLIES 5
v-ljerr-msft
Employee
Employee

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

Hi Folks,

Problem statement-

My data is getting updated on daily basis in Power BI but I want my graphs to refresh data on next month (09-09-2020) ( day is same for all months) now what measure should I use to achieve this in Page level filter/ graph level filter?

 

Thanks

Sumesh

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

Greg_Deckler
Super User
Super User

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

 

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

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.