How do I restrict data appearing in my matrix based on a Week No or Month ?

[acroft62]

Hi,

I want to filter the data appearing in 2 matrices according to the Week No and Month Name., such that when the report opens up these filters are automatically applied; subsequently a user (if they so desire) can change the filtered week/month name.

NB Assume current Week no and Month name are deriveable from the source and can be stored in aggregates/variables.

Thanks

Tony

[v-janeyg-msft · Accepted solution]

Hi, @acroft62 

If you have 'Week No' column and 'Month Name' column in your report, You can create two single lists(new tables) and use the two as slicers,then create a measure,use it in matrix filter pane.

Like this:

Table mon = DISTINCT('Table'[Month name])
Table week = DISTINCT('Table'[Week no])

Measure =
IF (
    SELECTEDVALUE ( 'Table mon'[Month name] )
        IN VALUES ( 'Table'[Month name] )
            && SELECTEDVALUE ( 'Table week'[Week no] ) IN VALUES ( 'Table'[Week no] ),
    1,
    0
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@acroft62 , You can have column like this on you date table and use that as filter

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

Month Type = Switch( True(),
Date([Date]) = eomonth(Today(),-1),"Last Month" ,
Date([Date])= eomonth(Today(),0),"This Month" ,
[Month Year]
)

refer the video : https://www.youtube.com/watch?v=hfn05preQYA

[acroft62]

Hi Amit, thanks for replying. So when I have my date columns as you suggest in your video, are you saying that I just need to set the slicers to This Week and This Month for the report to always open on on these values ?

[amitchandak]

@acroft62 , yes, based on today the values should change automatically.