Show next 6 weeks based on slicer selection

[joshua1990]

Hi all!

I have a historical sales table:

Article	Value	Date
Bike A	5000	01.05.2020
Bike B	5000	01.05.2020
Bike C	5000	01.05.2020

This table is linked to a simple calendar table.

Now I would like to create a matrix with Article on Row and Year-Week on columns.

Based on the Year-Week selection I would like to get just the next 6 weeks displayed.

When I select 2022-01 then it should be displayed this way:

Article	2022-01	2022-02	2022-03	2022-04	2022-05	2022-06
Bike A	0	0	0	0	600	700
Bike B	500	0	600	400	500	600

How would you do that in DAX?

I found this super article but that does not support WEEK selection:

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

[v-yueyunzh-msft]

Hi , @joshua1990 

According to your description , you want to show next 6 weeks based on slicer selection. Right?

Here are the steps you can refer to :

(1)This is my test data:

(2)We can create "Calendar" table and we don't need to create relationship between two tables:

Calendar = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('Sales'[Date]),LASTDATE('Sales'[Date])),
"year_week", year([Date]) * 100 + weeknum([Date])
)

 Then we need to click "New column" to create a calculated column in 'Calendar' table:

Flag = var _current_year_week = 'Calendar'[year_week]
var _t =DISTINCT( SELECTCOLUMNS( FILTER( 'Calendar' , 'Calendar'[year_week] <=_current_year_week) ,"year_week",[year_week]))
return 
COUNTROWS(_t)

(2)We need to create two calculated column in 'Sales' table:

year_week = year([Date]) * 100 + weeknum([Date])

Flag = LOOKUPVALUE('Calendar'[Flag],'Calendar'[year_week],'Sales'[year_week])

(3)Then we can create a measure :

value = var _slice_flag =MIN('Calendar'[Flag])
var _matirx  = MIN('Sales'[Flag])
return
IF( _matirx >= _slice_flag && _matirx <= _slice_flag+5 ,SUM('Sales'[Value]) , BLANK())

(4)We can put the 'Calendar'[year_week] in the slice and the 'Sales'[Article] and 'Sales'[year_week] and the [value] measure in the Matrix visual , and then we meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[joshua1990]

@v-yueyunzh-msft : Yes, this is perfect! But I missed one information. I also have a different dimension table that contains all articles. Is there any chance to use that dimension table on rows? 

[v-yueyunzh-msft]

Hi , @joshua1990 

If you want to put the fields of the dimension table on top of the rows, you just need to establish a one-to-many relationship with the fact table.

If this method does not help you, you can provide us with your complete table structure (.pbix or table format) so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly