March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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
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
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
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
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
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!!!
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") )))
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?
Can you share the pbix file containing the 445 calendar table creation. This will help answer your question.
Post the link here.
Cheers
CheenuSing
Hi
is there any way to make relative filters in the report, without coding or adding a column in the DB?
thanks
regards
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
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.
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
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?
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
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
91 | |
90 | |
83 | |
73 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |