Measure - Filter based on different table

[joshua1990]

Hi experts!

I have a sales table and an order table.

Simple tables that contains article, date and value. Both tables are linked to a calendar table.

Sales

Article	Date	Value
A	01.10.2022	500

Order

Article	Date	Value	Shared Date (Year-Week)
A	01.11.2022	500	2022-39
A	15.01.2022	600	2022-40

There is a slicer on the report page with "Shared Date". Now I would like to sum up the sales for the selected time horizon (Shared Date) including the next 4 weeks.

If I select 2022-39, then I would like to get the sales for 2022-39, 2022-40, 2022-41, 2022-42, 2022-43.

How would you do that in DAX?

[wdx223_Daniel · Accepted solution]

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]))

[wdx223_Daniel · Accepted solution]

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]))

[joshua1990]

@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

[wdx223_Daniel · Accepted solution]

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]))