Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table where I have a week column with yyyy-ww format. I do not have any date column. The week column contains data from last year until the next year. So, it is from 2020-01 to 2022-52. I want to create a mesure which shows only data for the last 5 weeks. For example, this is 20th week now and when I activate the filter, it shouls show data for week 15-19 with correct. It should also show the correct year at the end of the year. For example, if I am currently in W3 2022, it should show data for W50 2021 - W2 2022.
Any help or tips are appriciated!
Thanks!
shamo
Solved! Go to Solution.
Hi @shamo
Try this,
1. Copy the table as calendar table,
2. Drag the column in Calendar Table into slicer,
3. Then, create the SortColumn in two tables,
In Calendar Table:
SortColumn_Calendar = LEFT('calendar table'[YYYYWW],4)*100+RIGHT('calendar table'[YYYYWW],2)
In Table:
SortColumn_table = LEFT('Table'[YYYYWW],4)*100+RIGHT('Table'[YYYYWW],2)
4. Then, create the measure:
Measure_YYYYWW =
VAR _seldate =
SELECTEDVALUE ( 'calendar table'[SortColumn_Calendar] )
VAR _startdate =
MINX (
TOPN (
5,
FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
'Table'[SortColumn_table], DESC
),
'Table'[SortColumn_table]
)
VAR _enddate =
MAXX (
TOPN (
5,
FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
'Table'[SortColumn_table], DESC
),
'Table'[SortColumn_table]
)
RETURN
IF (
ISBLANK ( _seldate ),
SELECTEDVALUE ( 'Table'[YYYYWW] ),
IF (
MIN ( 'Table'[SortColumn_table] ) <= _enddate
&& MIN ( 'Table'[SortColumn_table] ) >= _startdate,
SELECTEDVALUE ( 'Table'[YYYYWW] ),
BLANK ()
)
)
Result:
See sample file attached bellow.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @shamo
Try this,
1. Copy the table as calendar table,
2. Drag the column in Calendar Table into slicer,
3. Then, create the SortColumn in two tables,
In Calendar Table:
SortColumn_Calendar = LEFT('calendar table'[YYYYWW],4)*100+RIGHT('calendar table'[YYYYWW],2)
In Table:
SortColumn_table = LEFT('Table'[YYYYWW],4)*100+RIGHT('Table'[YYYYWW],2)
4. Then, create the measure:
Measure_YYYYWW =
VAR _seldate =
SELECTEDVALUE ( 'calendar table'[SortColumn_Calendar] )
VAR _startdate =
MINX (
TOPN (
5,
FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
'Table'[SortColumn_table], DESC
),
'Table'[SortColumn_table]
)
VAR _enddate =
MAXX (
TOPN (
5,
FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
'Table'[SortColumn_table], DESC
),
'Table'[SortColumn_table]
)
RETURN
IF (
ISBLANK ( _seldate ),
SELECTEDVALUE ( 'Table'[YYYYWW] ),
IF (
MIN ( 'Table'[SortColumn_table] ) <= _enddate
&& MIN ( 'Table'[SortColumn_table] ) >= _startdate,
SELECTEDVALUE ( 'Table'[YYYYWW] ),
BLANK ()
)
)
Result:
See sample file attached bellow.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply. I think this calculates from the last week in the available data. But I want to have last 5 weeks from today.
I also want to add the measure to filter pane and when I select 1 then it shows the last 5 week and when 0 or not selected then normal data
@shamo , make sure you create a separate table for year week and then add rank column
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last 5 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |