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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Regular 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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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