- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Relative date filter for last n whole months
Hi,
is any option how to use relative date filter to filter some last whole calendar months including current month?
I'm not able set this filter for last month to work normally.
E.g. I need filter last 12 months, whole (calendar) months, and including this month. I want see monthly history for last year including current month.
When I used "is in the last" 12 "months" it correctly show current month, but show incomplete data of first month in row.
When I used "is in the last" 12 "calendar months" it correctly show full 12 months, but not show current month.
BTW When I use "is in this" "month", it show only this calendar month. But this filter is not possible combine (OR) with "in the last"
If I understand, there is possible filer by "month" (month period related to current day of month), or "calendar month" (whole months). And is possible compare with > or < or = operators to today. But there missing >= and <= operators?
Also is not possible combine this filter (with OR)
And also in Advanced filter, where is possible filter with >, >=, =, <= or <, but there is possible filter only to fixed data (not relative to today).
Same issue is for Date slicer.
It is really true?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello 🙂 I was after the same thing. In my date table, I added a calculated column to find the number of months compared to TODAY(), using DATEDIFF function. Because it's a column it can be used as a page or visual filter. It's been filtered to show records between 0 months ago (ie. current month) and 3 months ago. Good luck 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I know this workaround, but it is not usable. Users need switch between period types (month, quartal, year), and second I need for many date columns with default hierarchy. And last it is not solution but workaround, which need additional effort.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any new idea?
There is still not possible filter to last n calendar months including this month!
Also Anchor date in slicer options is still not possible set to calculated value!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
New Idea is cretated here, please vote:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=9578f230-ef1b-ee11-a81c-000d3a0ec312
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @josef78
I just had to implement the same business case in a project, and I founded an video from SQLBI that solve this problem regarding relative dates.
In my case, I add the "field" in Filter Panel for Page Filter level, not for the visual, and its worked very well for the entire page/report.
source:
https://www.youtube.com/watch?v=d8Rm7dwM6gc&ab_channel=SQLBI
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/
BR.
Lúcio
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is usable only if you have single (or few) measures, then you can chanllenge with this callculation.
But not usable if you have hundreds measures, or measures are managed by business.
Business want easy filter data for last N calendar months, including current month, do not spent hundreds hours of DAX programming.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you all,
I just wanted to confirm that this is not my misunderstanding of the relative date filter/slicer.
In fact, in the current version of Power BI, there is no easy way to realize the most common business requirement, filter for n whole months in a row, including the current month.
-In relative date filter/slicer missing option for "Less than or equal to".
-Anchor date option in relative date slicer is not working, when is set then slicer not work as relative.
Every workaround leads to curious DAX calculation, which need programing skills and making performance issues.
Fixing this issue is about one line of code on Microsoft side.
I create idea for it, please vote if agree:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The solution to this likely involves a measure. You can make one that checks if the current Date context meets your condition in an IF( ) to return 1 or 0. You can then use the measure to filter the visual.
Pat
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks.
No, it is not working, measures can NOT be used in filters and slicers.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I confirmed on my end that it does work. Use the measure below as a slicer for you visual with "is" 1.
ThisMonthPlusLast12Mos =
IF (
MAX ( 'Date'[Date] )
<= EOMONTH (
TODAY (),
0
)
&& MIN ( 'Date'[Date] )
>= EOMONTH (
TODAY (),
-13
) + 1,
1,
0
)
Pat
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is only True/False value, how user can change number of month in slilcer or filter? This solution not work.
Better workaround I described below, it work nice for small samples, but is not usable for real scenearios on large amount of data.
In the fact, Power BI does not support well know operators "<=" and ">=" in relative date filter.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
IF You can to compare the valor from two years, use the measure with the function "sameperiodlastyear(dates)"
For instance :
CALCULATE([Sum the present year],SAMEPERIODLASTYEAR(CALENDAR[DATA]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No, I don't want compare two years, I don't need measure (I know how caluculate in measure anything, but this not solving this issue).
I need "filter" data on everything on dashboard by date column, using filter or slicer, and I need n last "whole" months, including all of current month.
Currently I'm using very ugly workaround, I adding calculate columns with some fake date (which make some perfomance gap), where adding some amount (I'm adding one month to real date (to each date column), to create fake dates which work with date slicers). This only way how to overide missing "<=" with "<" operator in relative filter and slicer)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe, If you create a custon column with the value the 12 mounts ago with the function
12 months ago = DATE(YEAR('table1'[Date]),MONTH(table1[Date])-12,DAY(table1[Date]))
And you use it to apply a filter visual level for superior from them.
I'm not sure but maybe it works..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I think it is only ugly solution, I currently using.
I have calculated column:
DateCreatedFakeShift = dateadd(Table[DateCreated],-1,MONTH) and this column using in filter(or slicer) with combination "is in the last" n "calendar month", it works, but is problematic because:
-it need additional work, especially when I have multiple date columns, and it is not "seamless" as is proclaimed by Microsoft
-because calculated column is materialized, and for this column is created time intelligence date table with relation, it have major impact to performance
-it is very confusing for users, instead every single date column, see two columns, one normal date and second fake, and must know that normal date column must use in visualizations and tables, and fake column must use in filters and slicer.
-every normal analytic tool on the world knows comparing operators >, >=, =, <= and <. Power BI know only >, = and < ?
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
12-04-2023 04:16 AM | |||
06-19-2019 10:02 AM | |||
12-27-2023 01:25 AM | |||
10-24-2023 12:32 AM | |||
06-21-2018 01:37 AM |
User | Count |
---|---|
123 | |
79 | |
59 | |
58 | |
44 |
User | Count |
---|---|
179 | |
120 | |
82 | |
70 | |
53 |