Missing common options in Relative Date Filter/Slicer

[josef78]

Currently, when use Relative Date Filter or Slicer, when you want relative to calendar week, month or year, is possible use three operators "is in the last", "is in this" and "is in the next", which mean "<", "=" and ">". There completely missing ">=" and "<=" ("is in the last or in this" and "is in this or in the next") comparation.

Additionally, is not possible combine (with OR) these comparation (is not possible set "is in the last" n " "calendar months" OR "is in this" "calendar month", as is possible in advanced filter.

And last chance, in slicer is configuration option "anchor date" which can move the relative date and solve this issue, but this option is possible set only to fixed value. Is not possible set dynamically, e.g. = dateadd(today(),1,MONTH). Truly, if static only, I don't know what good is for (for relative date slicer).

Result? Currently is not possible filter date relative as common user want. Is not possible filter dashboard, and see e.g. last 12 whole months in row include current month.

But I hope, it is only bug, and will be soon fixed by adding well know comparators ">=" and "<="

[josef78]

Related topic Re: Relative date filter for last n whole months - Microsoft Power BI Community

[v-yingjl]

Hi @josef78 ,

Based on my test, the relative date filter in the last calendar week/month/year includes the definition of "<=" if there are related data in your data source:

Seems not a bug. In addition, you can can also use the date range filter in the visual level filter

Please refer: Creating a relative date slicer and filter in Power BI 

Best Regards,
Community Support Team _ Yingjie Li

[josef78]

No, it not work.

Based on your screenshots, you selected last two weeks. But your data not containing current week. Current week is from 2/21/2021to 2/27/2021 (in American numbering, in Europe is using another week numbering, which Power does not support, but it is another story), and there data are filtered out.

Same issue is for calendar months, most common what business want to see, last 12 whole calendar months (including current), when set using slicer (same is in filter):

... user not see data from current month - February (the most important value of this month). Applied operator is only "<", not "<=" as you say.

There not easy (for business people) way how to report last N calendar months including current month.

There exist numerous workarounds (see link), but all of them need programing skills and make performance impact to report. It's not seamless and easy as is proclaimed by Microsoft.

This is bug, may be not programming bug, but sure bug in design of this component.

To you addition, range filter work, but it work only on daily bias, use this filter to filtering whole months or weeks is mission impossible. But most important, is it not relative, there more options how to static filter data, but there not usable option to filter relative, most common what business want, see last N whole months, including this month.

[v-yingjl]

Hi @josef78 ,

Use Months(Calendar) as an example. Currently in power bi, if you choose Months (Calendar), then the period always consider full calendar months. which means it will start from 1st of Jan until end of Jan, because Feb in the table is not still a full calendar month.

The end date of Feb 2021 is 2021/2/14

Please refer this article which introduces it in details: Relative Date Slicer in Power BI; Simple, yet Powerful 

If you want the logic is "<=" as your mentioned, perhaps you can create a new idea here and vote for it. Currently it is by designed in power bi, not a bug.

Best Regards,
Community Support Team _ Yingjie Li

[josef78]

Yes, I understand, but I think, it is still "by design" bug.

Currently, when I choose "Last" n "Months (Calendar)", Power BI return n last months, but only full completed months in the past.

But business understanding and business requirements are different.

If business want see whole current month, it mean data from whole current month, today (22/02/2021) it mean from 1/02/2021 to 28/02/2021 (no matter that is not completed)

If business want see last 12 whole months, it mean data for last 12 whole month, including whole (incomplete) current month, today(22/02/2021) it mean from 1/03/2020 to 28/02/2021 (no matter that is not completed).

And be sure The end date of Feb 2021 is not 2021/2/14

Business typically need see daily progress of current month with compare with last n month. Or maybe with some forecast, but again last n months including current month. For business is uninteresting information about month competed in the past without current information. At this century business typically work with interactive dashboard on daily basis (not only at first day of month).

Yes in '90 when DWH processed data once per month and business see data few weeks later, it was ok to see only completed months in the past. But now is year 2021 and be able to see historical values with current one, is most common option.

Additionally reason, in Power BI all filter are connected with AND operator, and is impossible to some relative filter (with "<") add (with OR connection) also current month filter. When will relative filter works with "<=", is easy add second filter to filter out current month.

The most important message. In Power BI (which proclaimed as seamless and easy by Microsoft) is not possible with any easy way, to use for relative filter date data, to show common business requirement "show last n whole months, including current".

I think, that it is not new idea, I don't want add some new like role-playing dimensions (which is high effort idea), it is only correction of some misunderstanding of business problem in developer team of some older good idea. And it is about adding one line of code to Power BI. I don't want change current function, but only add one more option, like "Last or This" (in slicer) or "is in the last or in this" (in filter). However, this topic is IDEA topic.

[josef78]

Hello @v-yingjl , Hello anyone...

Is there any new idea?

Is year 2022 and Power BI relative slicer still do not support "<=" and ">=" operators, but only "<", ">" and "=". 

It is terrible. 

[JJJJJJJJ]

This seems to be the active idea currently being voted on if you've not already added your vote. 

https://ideas.powerbi.com/ideas/idea/?ideaid=3a4f1ba4-1b70-4fde-8cbc-6da444d11ad6

It currently needs more - so if you've only voted on the community page, then I think a vote on this will also help.

[josef78]

Is there any new think?

Is year 2023 and Power BI relative slicer still do not support "<=" and ">=" operators, but only "<", ">" and "=". 

There is also unusable and unfinished Anchor date setting, where you can add data only to fixed date in design mode, and canNOT set to calculated value (if I want use fixed date filter, I don't need relative slicer, but if I want dynamically (relative) filter, is not possible.

It is terrible. 

[josef78]

New "Idea" is here, please vote:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=9578f230-ef1b-ee11-a81c-000d3a0ec312