Reply
Highlighted
Frequent Visitor
Posts: 5
Registered: ‎12-19-2018
Accepted Solution

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


Accepted Solutions
Frequent Visitor
Posts: 5
Registered: ‎12-19-2018

Re: Dynamic List by Date range

@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 Smiley Happy,
Chris

View solution in original post


All Replies
Super User
Posts: 10,512
Registered: ‎07-11-2015

Re: Dynamic List by Date range

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


AlB Super Contributor
Super Contributor
Posts: 1,183
Registered: ‎11-12-2018

Re: Dynamic List by Date range

Hi @thechrispybacon

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. 

Frequent Visitor
Posts: 5
Registered: ‎12-19-2018

Re: Dynamic List by Date range

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. 

Frequent Visitor
Posts: 5
Registered: ‎12-19-2018

Re: Dynamic List by Date range

@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 Smiley Happy,
Chris