The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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])))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
75 | |
62 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |