topic Measure - Filter based on different table in DAX Commands and Tips

Measure - Filter based on different table

joshua1990 — Sun, 09 Oct 2022 17:25:54 GMT

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?

Re: Measure - Filter based on different table

wdx223_Daniel — Mon, 10 Oct 2022 06:39:43 GMT

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

Re: Measure - Filter based on different table

joshua1990 — Mon, 10 Oct 2022 13:45:44 GMT

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

Re: Measure - Filter based on different table

wdx223_Daniel — Tue, 11 Oct 2022 00:15:23 GMT

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