Forum Discussion
Week filter
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
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
1 Reply
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