Forum Discussion
Measure - Filter based on different table
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?
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]))
3 Replies
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 : 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]))
