Week filter

[Barculez]

Hey,

I would like to ask you for help with a filter, I have a dashboard where are 4 charts - it is a weekly report, one chart shows data for the last 12 weeks, 2 charts for last 4 weeks and one is for the current week.

Is it possible to create 1 filter for all of them (ideally for the whole set) where the current week could be selected and the other charts would subtract the weeks? Or is there a solution that would be simple to filter such charts?

Thank you 

[Anonymous · Accepted solution]

Hi  @Barculez ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Slicer = 
IF(
    WEEKNUM('Table'[Date],2) = WEEKNUM(TODAY(),2),"Current Week","W"&""&WEEKNUM('Table'[Date],2))

2. Create calculated table.

Slicer_Table =
DISTINCT(
    'Table'[Slicer])

3. Create measure.

Flag_current =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
    IF(
_select="Current Week" ,
SUMX(
    FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)),[rand]),
SUMX(    FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))),[rand]))

Flag_4 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
    IF(
_select="Current Week" ,
SUMX(
    FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-4),[rand]),
SUMX(    FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-4),[rand]))

Flag_12 =
var _select=SELECTEDVALUE('Slicer_Table'[Slicer])
return
    IF(
_select="Current Week" ,
SUMX(
    FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)= WEEKNUM(TODAY(),2)-12),[rand]),
SUMX(  FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&WEEKNUM('Table'[Date],2)=VALUE(RIGHT(_select,LEN(_select)-1))-12),[rand]))

4. Result:

Select Current Week:

Select W17:

Best Regards,

Liu Yang

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