Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎06-22-2016
Accepted Solution

Filter Dates to only show current month and next 12 months

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. BITable.PNG

 

Thanks, 

James 


Accepted Solutions
Established Member
Posts: 146
Registered: ‎06-15-2016

Re: Filter Dates to only show current month and next 12 months

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:

Capture.PNG

 

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

 

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

 

Capture2.PNG

And

 

 

View solution in original post

Regular Visitor
Posts: 25
Registered: ‎04-12-2016

Re: Filter Dates to only show current month and next 12 months

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

View solution in original post

New Contributor
Posts: 492
Registered: ‎02-17-2016

Re: Filter Dates to only show current month and next 12 months

@jhowe

 

I assume that you want to show the current and future Sales for the next 12 months in a chart.

 

Try the following

 

1. I am assuming you have a date table

2. Create a Column in the date table  called as MonthSequentialNumber as

     MonthSequentialNumber = year( date in date table ) * 12 + Month( date in date table) - 1

     This will create a unique number for each combination of month and year.

3. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

    This will find the MonthSequentialNumber based on todays date. 

4. Create a column in Date table called Show as

      Show = IF (
                          [MonthSequentialNumber ] >= [CurrentMonthSeqeuntialNumber ] &&

                          [MonthSequentialNumber ] <= [CurrentMonthSeqeuntialNumber ] + 11, 1, 0
                          ) 

5. Show will  have a value of 0 or 1 in the date table.

6. Assume you have a measure called [Sales] that calculates the sum of Sales . As per your requirment I am assuming your sales records contains future predicted sales.

7. Create a bar chart  where x-axis is Month and Y- axis is Sales measure.

8. In the visual level filter for the bar chart drag the field Show and set the filter condition show items when the value is 1.

9. You will then be able to see the Sales for the current month and the future 12 months from Now.

 

Should you require further assistance , please do not hesitate to reply to this post.

 

If this works for you please accept it as a solution and also give kudos.

 

Cheers

 

CheenuSing

 

View solution in original post


All Replies
Established Member
Posts: 146
Registered: ‎06-15-2016

Re: Filter Dates to only show current month and next 12 months

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:

Capture.PNG

 

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

 

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

 

Capture2.PNG

And

 

 

Regular Visitor
Posts: 25
Registered: ‎04-12-2016

Re: Filter Dates to only show current month and next 12 months

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

New Contributor
Posts: 492
Registered: ‎02-17-2016

Re: Filter Dates to only show current month and next 12 months

@jhowe

 

I assume that you want to show the current and future Sales for the next 12 months in a chart.

 

Try the following

 

1. I am assuming you have a date table

2. Create a Column in the date table  called as MonthSequentialNumber as

     MonthSequentialNumber = year( date in date table ) * 12 + Month( date in date table) - 1

     This will create a unique number for each combination of month and year.

3. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

    This will find the MonthSequentialNumber based on todays date. 

4. Create a column in Date table called Show as

      Show = IF (
                          [MonthSequentialNumber ] >= [CurrentMonthSeqeuntialNumber ] &&

                          [MonthSequentialNumber ] <= [CurrentMonthSeqeuntialNumber ] + 11, 1, 0
                          ) 

5. Show will  have a value of 0 or 1 in the date table.

6. Assume you have a measure called [Sales] that calculates the sum of Sales . As per your requirment I am assuming your sales records contains future predicted sales.

7. Create a bar chart  where x-axis is Month and Y- axis is Sales measure.

8. In the visual level filter for the bar chart drag the field Show and set the filter condition show items when the value is 1.

9. You will then be able to see the Sales for the current month and the future 12 months from Now.

 

Should you require further assistance , please do not hesitate to reply to this post.

 

If this works for you please accept it as a solution and also give kudos.

 

Cheers

 

CheenuSing

 

Frequent Visitor
Posts: 4
Registered: ‎06-22-2016

Re: Filter Dates to only show current month and next 12 months

Thank you very much for your instructions. I had tried making a custom filter thinking that it would give me the most options but found it to be lacking the abilities I needed but maybe that is only because I have been working with BI for about a month now. I think your method will provide a simple solution to exactly what I need, thank you!

New Member
Posts: 1
Registered: ‎06-29-2016

Re: Filter Dates to only show current month and next 12 months

Hi

is there any way to make relative filters in the report, without coding or adding a column in the DB?

 

thanks

regards

aka Regular Visitor
Regular Visitor
Posts: 46
Registered: ‎03-22-2017

Re: Filter Dates to only show current month and next 12 months

@jahida

hi, I have the similar situation, In my case i have to show past two months and the current month and have applied the filter accordingly as shown by you, and it working fine, buit i had just one question  will this approch automatically filter out months, as in will it update the filter when the month changes or we need to schedule referesh or somehting.

 

Thanks

Established Member
Posts: 146
Registered: ‎06-15-2016

Re: Filter Dates to only show current month and next 12 months

Hey,
It'll update automatically, as long as you refresh your data.
Regular Visitor
Posts: 17
Registered: ‎05-29-2017

Re: Filter Dates to only show current month and next 12 months

But his hides all the other dates in the dataset, i want if i can by default select the today's date and slicer should also allow selection of other dates as well, possible?

Frequent Visitor
Posts: 3
Registered: ‎08-02-2016

Re: Filter Dates to only show current month and next 12 months

Thanks for the solution. I have used different approach that has  worked as well. The problem is when I am using 445 calendar. Month calc in Today() function might come to 12 but in my 445 calendar, the actual date today can be fiscal month 11. How do you solve this problem?