Filter Dates to only show current month and next 12 months

[jhowe]

I am trying to create a more dynamic date filter for a report of mine that shows past and predicted data. I would like a filter that only shows data related to the current month and the12 months to follow. For example from June 2016 to June 2017. But I would like BI to be able to recognize what the current month is so that when the next month begins it can automatically adjust the filter. Meaning that when June ends I would like BI to automatically adjust the filter to show July 2016 to July 2017. I would really appreciate any help or ideas people have. I have attached an image of my current table. 

Thanks, 

James 

[jahida · Accepted solution]

Hi,

PowerBI has filtering wizards, so I could provide you with M code but I think it's more useful for you if I show the process for using the wizard:

I'm using a dataset of mine, with a date column. Click on the down arrow with the column header:

Then go to Date/Time filters -> In the Next...

That should open up a window where you can select these options:

And

[felyoubi · Accepted solution]

Hi,

You have two ways in doing this, M or DAX. I prefer the latter because it is straightforward and it doesn't require refresh of the data model. Here we go:

DAX:

Current Month = IF(MONTH(DimDate[Date])=Month(NOW()), "Current Month", Format([Date], "YYYY mmmm"))

Select a slicer and sort by date, this will make sure it puts Current Month always on top.

M:

= Table.AddColumn(MonthName, "CurrentMonth", each Date.IsInCurrentMonth( [Date] ))

Regards,

Fahd