How to write a filter to show data of the month after next month

[Anonymous]

Hi,

I am working to build a matrix to show data of the month after next month and can automatically update every month. For example, now it's June, so this will show all data of August. When it's July, this will auto update to show September's data.

I think I need to use a measure as a filter, but have no idea how to write it. May you help me?

Many thanks!

[jdbuchanan71]

Hello @Anonymous 

Give this a try

Month +2 = 
VAR SelectedDate = MAXX ( VALUES ( Table1[DATE] ) , [DATE] )
RETURN FORMAT ( DATE ( YEAR ( SelectedDate ), MONTH ( SelectedDate )+2, 1), "mmmm" )

[Anonymous]

Thanks for kind help!

But I still don't see how this can update data every month. I mean, should we include something like month(today()) ? And what does '1' mean in your FORMAT function?

Thanks so much!

[jdbuchanan71]

Whatever month you have selected, this measure will return 2 months from that.  

The part of the formula with the 1 is just calcing the date with 2 months added and returning the month name.

So, if you picked June 20th 2019 in your data table

Selected Date will be 6/20/2019

• DATE ( YEAR ( 6/20/2019 ), MOTH ( 6/20/2019) +2, 1)
• DATE ( 2019, 6 + 2, 1 )
• DATE ( 2019, 8, 1)
• 8/1/2019
• FORMAT ( 8/1/2019, "mmmm" )
• = August

[Anonymous]

I totally understand the code and learn a lot from it, thanks so much!

But this still cannot auto update. I mean, when it's June, I want it show data of August automatically; when it becomes July, it will show September automativally. For your code, I need to change in filter from 'is August' to 'is September' to get the same result, but this is not automatically.

Thanks again!!  

[jdbuchanan71 · Accepted solution]

Ahh, what you need in addition is a column in your date table that calculates for "Current Month".

Month - Year = 
VAR CurrentYear =
    YEAR ( TODAY () )
VAR CurrentMonth =
    MONTH ( TODAY () )
RETURN
    IF (
        MONTH ( Dates[Date] ) = CurrentMonth
            && YEAR ( Dates[Date] ) = CurrentYear,
        "Current Month",
        FORMAT ( Dates[Date], "mmm-yyyy" )
    )

Every time your model refreshes this column will calculate and tag the current month as "Current Month".  Then, in your slicer just pick "Current Month" and it will shift the date range automatically as you go from month to month.

On July 1st the column [Month - Year] for all the rows in June will show "Jun-2019" and all the rows in July will show "Current Month".

[Anonymous]

I have a similar question. I got it to always select the current month, however, how can I add to this code to also automatically select previous month and same month last year? I couldn't find anything when researching. I just want to expand on the code below if possible.

Report Month = IF(MONTH(now()) = Month(DateTable[date]) && YEAR(now()) = YEAR(DateTable[date]), "Current Month", DateTable[Month_year])

[Anonymous]

I've added something to your code to deal with Month11&1 and 12&2.

Man, you are great!