Forum Discussion

marenecaCZ's avatar
marenecaCZ
Frequent Visitor
3 years ago

Use measure in filter pane

Hello,

I created matrix visual like this:

Weeks are in columns.

I would like to display only last 4 weeks without last week. In my case it is week5 till week8 (=4 weeks). Only these 4 weeks I would like to have in visual.

In "Filters on this visual" I can do it this way:

 

But new data column will be added every week. After 5 more weeks I will have weeks (columns) in matrix from 1 to 14. But I still need to display last 4 weeks without the last week (= from week10 till week13). I don't want to change the "Filters on this visual" every week.

Can I set the filter somehow automatically?

 

I create new mesure for the maximum week = weeknum(today()) -1
I create new mesure for the minimum week = weeknum(today()) -4

But it's not posible to use this measures instead of numbers in "Filters on this visual".

 

Thank you

 

 

 

Need Help

1 Reply

  • krishb1414's avatar
    krishb1414
    Icon for Helper III rankHelper III
    3 years ago

    Hi marenecaCZ  
    => Firstly create one new table with this code 

             SUMMARIZE(dim_Date,dim_Date[Weeknum],"max date",FORMAT( MAX(dim_Date[Date]),"dd-mmm-yy" ))
    => Next use lookup function in main table to get max date from table (with above code)
        week =   LOOKUPVALUE('Summarize'[max date1],'Summarize'[Weeknum],'dim_Date'[Weeknum])
    => then make this format as date 
    => finally use this week in filter pane and put filter as last 4 weeks.
    Then in future also it will dynamically filter only 4 weeks, if you want you can chnage 6 weeks, 8 weeks......
    See the image below