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
Anonymous
Not applicable

Dynamic List by Date range

Hi Power BI community, first of all I would like to wish you a happy new year. 

So for one of my reports I have the requirement to show a list of Categories, that show up in a date range.  So depending on the selected month in the slicer, I want to see two years back and one year into the future. 

The categories should be shown in a table. The relative date slicer works in this scenario, but I want the user to only have to select one month. 

 

Does anyone know a way how to do this, or whether it is even possible at all?

 

Greetings,

Chris

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@AlB

Thank you for the solution. However it didn't work for me right away,but put me on the right path.

So what I had to do to fix this was using a measure as a filter like you did.

RollingYearList = 
VAR RollingMonths = 36
VAR DataTableDate = MIN(DataTable[Date])


VAR DateTableDate = EDATE(MIN(FilterTime[Date]); 11)
VAR DateAddAlternative = EDATE(DateTableDate;-RollingMonths)
RETURN

    IF(
        (DataTableDate < DateTableDate)  && 
        (DataTableDate > DateAddAlternative)  ;
        -- FLAG --
        1
        )  

This article by Radacad describes the measure in more detail: http://radacad.com/dynamic-date-range-from-slicer

I had to deactivate the relationship of my date table and data table, and create a new date table (FilterTime) which I used to filter my data table. That new date table is only connected to my original date table, so that I can filter it thorughout my report.
If I want to do any other calculations I just use the USERELATIONSHIP statement to connect the two tables for the measures where it is required. 

Then I created a matrix in which I put my Category Values and Date Values of my data table in the "Rows" section (leaving all other inputs blank)  and put the measure (RollingYearList) as a Visual Filter with "is 1".   When just using a regular table, not adding the date values and applying this filter I would get incorrect results for some reason. That way I always get the categories that are in the three year date range of the particular month that I selected. Users can then perform a drill down where they can see in which months my categroies showed up. I didn't want this so I put a transparent rectangle of my matrix. 

Have a nice weekend :),
Chris

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @Anonymous

You could do the following:

1. Create a Date table and create a relationship with your data table

2. Place Month in a slicer

3. Place Category in the rows of a matrix visual

4. Create a measure that will return TRUE when the category is found within the selected dates and FALSE otherwise:

 

Measure =
CALCULATE (
    CONTAINS ( Table1, Table1[Category], SELECTEDVALUE ( Table1[Category] ) ),
    DATESBETWEEN (
        'Date'[Date],
        FIRSTDATE ( NEXTDAY ( DATEADD ( 'Date'[Date], -2, YEAR ) ) ),
        LASTDATE ( DATEADD ( 'Date'[Date], 1, YEAR ) )
    )
)

5. Place the measure in visual level filters and select to show only when TRUE

 

Note:  have a good look at what dates exactly you want to include in DATESBETWEEN. It may not be what I've written but this gives you the idea and you can update it as required. 

Anonymous
Not applicable

@AlB

Thank you for the solution. However it didn't work for me right away,but put me on the right path.

So what I had to do to fix this was using a measure as a filter like you did.

RollingYearList = 
VAR RollingMonths = 36
VAR DataTableDate = MIN(DataTable[Date])


VAR DateTableDate = EDATE(MIN(FilterTime[Date]); 11)
VAR DateAddAlternative = EDATE(DateTableDate;-RollingMonths)
RETURN

    IF(
        (DataTableDate < DateTableDate)  && 
        (DataTableDate > DateAddAlternative)  ;
        -- FLAG --
        1
        )  

This article by Radacad describes the measure in more detail: http://radacad.com/dynamic-date-range-from-slicer

I had to deactivate the relationship of my date table and data table, and create a new date table (FilterTime) which I used to filter my data table. That new date table is only connected to my original date table, so that I can filter it thorughout my report.
If I want to do any other calculations I just use the USERELATIONSHIP statement to connect the two tables for the measures where it is required. 

Then I created a matrix in which I put my Category Values and Date Values of my data table in the "Rows" section (leaving all other inputs blank)  and put the measure (RollingYearList) as a Visual Filter with "is 1".   When just using a regular table, not adding the date values and applying this filter I would get incorrect results for some reason. That way I always get the categories that are in the three year date range of the particular month that I selected. Users can then perform a drill down where they can see in which months my categroies showed up. I didn't want this so I put a transparent rectangle of my matrix. 

Have a nice weekend :),
Chris

Greg_Deckler
Super User
Super User

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler,


 

thanks for the nice pbix sample file. Initially I was running into issues with my date calculations because of the date granularity. When calculating the number of Categories, it seems to work fine. But once I try to create a list of the categories it doesn't work. 

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.