Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
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 🙂
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.
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!
New Idea is cretated here, please vote:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=9578f230-ef1b-ee11-a81c-000d3a0ec312
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
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.
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:
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks.
No, it is not working, measures can NOT be used in filters and slicers.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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]))
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)
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..
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 < ?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
167 | |
136 | |
134 | |
100 | |
86 |