Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 depe...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

AlB's avatar
AlB
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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