Forum Discussion
Post ProdigyShow next 7 weeks based on Selection
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 ?
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*11var cdate = MAX('Calendar'[Date])returnIF(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/
1 Reply
- ValtteriN
Community Champion
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*11var cdate = MAX('Calendar'[Date])returnIF(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/