Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
josef78
Memorable Member
Memorable Member

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?

 

 

 

15 REPLIES 15
jr2482
Frequent Visitor

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.

josef78
Memorable Member
Memorable Member

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!

luandrad
Helper I
Helper I

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.

josef78
Memorable Member
Memorable Member

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:

https://community.powerbi.com/t5/Issues/Missing-common-options-in-Relative-Date-Filter-Slicer/idi-p/...

mahoneypat
Microsoft Employee
Microsoft Employee

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


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.

 

mahoneypat_0-1613861921403.png

 

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


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.

Portrek
Resolver III
Resolver III

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 < ?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.