Forum Discussion
Anonymous
3 years agoNot applicable
relative week filter
Hi, in my report I have bar chart with weeknr as x-axel ex 202223, I want have a weeknr range like from current week+20, example: current week is 202244 so my x axel will be like 202244, 202245,2022...
- 3 years ago
Here is one way.
Based on this sample data
I created a dimension table for weeks including an index column which we will use to filter the visual with a measure:
Week Table = ADDCOLUMNS ( VALUES ( 'Table'[Week] ), "Index", RANKX ( VALUES ( 'Table'[Week] ), 'Table'[Week],, ASC ) )Create the following measure to add to the filters on the visual, settin the value to equals 1.
Filter weeks = VAR _TodayWeek = YEAR ( TODAY () ) * 100 + WEEKNUM ( TODAY () ) VAR _CurrWeekIndex = CALCULATE ( MAX ( 'Week Table'[Index] ), FILTER ( ALL ( 'Week Table' ), 'Week Table'[Week] = _TodayWeek ) ) RETURN COUNTROWS ( FILTER ( 'Week Table', 'Week Table'[Week] >= _TodayWeek && 'Week Table'[Index] <= _CurrWeekIndex + 20 ) )Sample PBIX file attached
PaulDBrown
Community Champion
3 years agoHere is one way.
Based on this sample data
I created a dimension table for weeks including an index column which we will use to filter the visual with a measure:
Week Table =
ADDCOLUMNS (
VALUES ( 'Table'[Week] ),
"Index", RANKX ( VALUES ( 'Table'[Week] ), 'Table'[Week],, ASC )
)
Create the following measure to add to the filters on the visual, settin the value to equals 1.
Filter weeks =
VAR _TodayWeek =
YEAR ( TODAY () ) * 100
+ WEEKNUM ( TODAY () )
VAR _CurrWeekIndex =
CALCULATE (
MAX ( 'Week Table'[Index] ),
FILTER ( ALL ( 'Week Table' ), 'Week Table'[Week] = _TodayWeek )
)
RETURN
COUNTROWS (
FILTER (
'Week Table',
'Week Table'[Week] >= _TodayWeek
&& 'Week Table'[Index] <= _CurrWeekIndex + 20
)
)
Sample PBIX file attached