Forum Discussion
Measure - Filter based on different table
please try these steps
1, add a column in the calendar table, Year-Week
2, create a measure
=VAR _yw=selectedvalue(orders[Shared Date(Year_Week)])
VAR _selectedweeks=topn(4,filter(allselected(calendar[year-week]),calendar[year-week]>=_yw),calendar[year-week],ASC)
return
calculate(sum(Sales[Value]),treatas(_selectedweeks,calendar[year-week]))
try these steps
1, add a new dimension table, say YearWeek, as the slicer table with no relationships.
YearWeek=Values(orders[Shared Date(Year_Week)])
2, put the calendar[Year-Week] on the visual, and amend the meausre
=VAR _yw=selectedvalue(YearWeek[Shared Date(Year_Week)])
VAR _selectedweeks=topn(4,filter(allselected(calendar[year-week]),calendar[year-week]>=_yw),calendar[year-week],ASC)
return
if(max(calendar[year-week]) IN _selectedweeks,sum(Sales[Value]))
wdx223_Daniel : That works perfectly fine! Thanks.
Is it possible to display/ split this result for the weekly aggregation? Maybe using a second calendar table?
I would like to see the sum per week.
If I use a second calendar table, then I get the correct values per week. But every other week is also displayed but with a 0
try these steps
1, add a new dimension table, say YearWeek, as the slicer table with no relationships.
YearWeek=Values(orders[Shared Date(Year_Week)])
2, put the calendar[Year-Week] on the visual, and amend the meausre
=VAR _yw=selectedvalue(YearWeek[Shared Date(Year_Week)])
VAR _selectedweeks=topn(4,filter(allselected(calendar[year-week]),calendar[year-week]>=_yw),calendar[year-week],ASC)
return
if(max(calendar[year-week]) IN _selectedweeks,sum(Sales[Value]))