Forum Discussion

Stabbathehut's avatar
Stabbathehut
Frequent Visitor
6 years ago
Solved

Dynamic filter for year

Hi All, I would like to show documents filtered for differing years dynamically.     So I would like to have a measure which only shows 2020 or 2022. How would I go about this.   Thanks i...
Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Stabbathehut 

    Do you add [date] column from a date table, then add"corresponding documents" to the same table visual?

    If so, you could create a relationship between two tables.

    For example, my date table is create as below:

    date table = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2022,12,31)),"year",YEAR([Date]),"month",MONTH([Date]))

add a calculated column
firstdayperyear = CALCULATE(MIN('date table'[Date]),ALLEXCEPT('date table','date table'[year]))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Stabbathehut's avatar
Stabbathehut
Frequent Visitor
6 years ago

Hi I am trying to say, if date = 2020 I would like to show the values corresponding to it. So in the image below it only shows 2020 with the corresponding documents

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi Stabbathehut 

Do you add [date] column from a date table, then add"corresponding documents" to the same table visual?

If so, you could create a relationship between two tables.

For example, my date table is create as below:

date table = ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2022,12,31)),"year",YEAR([Date]),"month",MONTH([Date]))

add a calculated column
firstdayperyear = CALCULATE(MIN('date table'[Date]),ALLEXCEPT('date table','date table'[year]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.