Show next 7 weeks based on Selection

joshua1990 — Sun, 13 Nov 2022 14:53:06 GMT

Hi all!

I hve a simple sales table that is linked to a calendar table.

Now I would like to create a report (matrix visual) that shows me per article (row) the sales per week (column) based on week selection.

If I select WK01, then I would like to see it this way:

Article	WK01	WK02	WK03	WK04	WK05
A	1	3	0	4	3
B	1	3	0	4	4
C	1	3	0	44	4
D	1	3	0	9	3
E	1	0	0	9	5

How would you do that?

Do I need a second calendar table ?

Re: Show next 7 weeks based on Selection

ValtteriN — Sun, 13 Nov 2022 17:00:44 GMT

Hi,

I would utilize a filter measure:

E.g. this measure shows the next 11 weeks:

11weeks = var _sdate =CALCULATE(MIN('Calendar'[Date]),ALL('Calendar'[Date]),

WEEKDAY('Calendar'[Date],2)=1,

WEEKNUM(TODAY())=WEEKNUM('Calendar'[Date]),

'Calendar'[Date]>TODAY()-7)

var _edate = _sdate + 7*11

var cdate = MAX('Calendar'[Date])

return

IF(cdate>=_sdate&& cdate<=_edate,1,0)

You can make the measure to be dynamic by using another calendar and then referencig that in the dax.

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

topic Show next 7 weeks based on Selection in DAX Commands and Tips

Show next 7 weeks based on Selection

Re: Show next 7 weeks based on Selection