Display product name by last week, Week-2, Week-3 ...

[Anonymous]

Hello,

i need some help.

I have data like that : 

For example : in Green it's the data from previous week, in red from 2 weeks ago, etc...

So i want the last 3 weeks from today

 And i would like to be like that in Power BI

Thanks for you help.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could enter another table like below:

Then create a measure:

diff = DATEDIFF(MAX('Table'[Date]),TODAY(),WEEK)

Measure = 
SWITCH(MAX('Table (2)'[WEEK]),"Week 1",CALCULATE(MAX('Table'[Product]),FILTER('Table',[diff]=1)),
"Week 2",CALCULATE(MAX('Table'[Product]),FILTER('Table',[diff]=2)),
"Week 3",CALCULATE(MAX('Table'[Product]),FILTER('Table',[diff]=3)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, 

Thanks for the answer.

I had some issues with your solution because my tables are a little different but i managed to make it works somehow with your help

Thank you.

Have a nice day.

[amitchandak]

@Anonymous , Create a week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

Then create a rank column

rank = rankx(filter(Table, Table[Week Start date] =earlier([Week Start date]) ), [Product])

Now in a matrix visual

Rank Rank on row , Date on Column, and max of product as value

[Anonymous]

Hello, thanks for the answer. 

It works somehow but not like as i wanted.

I'm sorry, it might be a little long.

I have some issues :

1) if i have for example 10 weeks of data, it will create 10 columns for each week but what i want it's only the 3 last week.

2) in fact my data looks like that :

But if i filter it by actual stock = "-" 

Then i got that : (i want to get rid of the empty cells and move the others num product higher

3) I want to rename myself these columns by week-2 and week-1 but i can't do that here

 (Usually i have 1 field for 1 column) but here i have one field 'date' for x Column

Normally, I rename them here :

4) It is possible to have this table without the column rank? just 3 column, week-3, week-2 and week-1?

Thanks again