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

[CheenuSing · Accepted solution]

@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

[immani]

Dear, 

I am trying to add the filter on the visual which has the data from august 2023 to january 2024, when i use the cureent month query which is 

Current Month = IF(YEAR('Dim - Date'[Dates])=YEAR(TODAY()) && MONTH('Dim - Date'[Dates])=MONTH (Max('Dim - Date'[Dates]) ) , 1,0 )


it filters the current month but for previous months it shows zero value when slected on the filter. i want to show the current month by default as welll as the presvious month values when selected on the filter

[JoSwinnen1]

Hi, 

I've tried to use this solution and it works perfectly when the 11 is fixed, but how can I make this variable?

I've tried to make a table with numbers between 1-50 and make a slicer of this column. Then I've changed the formula for show to: 

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

                          [MonthSequentialNumber ] <= [CurrentMonthSeqeuntialNumber ] + SelectedValue('Parameter'[Parameter]), 1, 0
                          ) 

But it doesn't work. It only gives 1 for the CurrentMontSequentialNumber. Am I doing something wrong or is there another solution?

thanks!

Jo 

[dsinha]

Hi,

I have a similar requirement where the latest month and year in the chiklet slicer should be selected when the report opens up.

Currently I have set the property of the slicers as forced selection  so that one value will always be selected by default when the report opens up.Now if I select the current month and year and save the report then I can achieve it.But that is a temporary solution because when a new year or month gets added up in the data,it is not impacting.So I would like to know if we can set a default value to chiklet slicers and if it is possible would like to know the workarounds.

Thanks in advance!!!

[QaziArfeen]

This is really helpful. I used this to create new column which shows "Current Month", "Previous Month" and "Next Month" values and rest as month values

Month Silcer = IF (MonthDimension[MonthSequentialNumber]= MonthDimension[CurrentMonthSeqeuntialNumber] ,"Current Month",
     IF ((value(MonthDimension[MonthSequentialNumber])-1)= MonthDimension[CurrentMonthSeqeuntialNumber],  "Next Month",
         IF ((value(MonthDimension[MonthSequentialNumber])+1)= MonthDimension[CurrentMonthSeqeuntialNumber],  "Previous Month", Format(MonthDimension[MonthColumn], "MMMM") &" " &Format(MonthDimension[MonthColumn], "YYYY")  )))

[bikashmittle]

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?

[CheenuSing]

Hi @bikashmittle

Can you share the pbix file containing the  445 calendar table creation.  This will help answer your question.

Post the link here.

Cheers

CheenuSing

[Maxmax76]

Hi

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

thanks

regards

[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

[chandrakant_m]

The current implementation is working as expcted only issue is we want to show current month text instead of 'Current Month'. Want to show 'January' . Please let met know how to achive same.

[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

[shzyincu]

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?

[aka]

@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

[jahida]

Hey,
It'll update automatically, as long as you refresh your data.

[jhowe]

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!