How to summarize data for last week in separate column with condition?

[slavisha84]

Hello, I have a table that has 3 columns: Week, Name and Points. 

I need to create another table in which i am going to store data by Name for where last 4 weeks going to have their own columns with points for that week. 
Here is the challenging part, if the Name has less than 200 points in the last available week, the name will not be in the list in the new table. 

It should be like this:

As you can see Alabama and California are not on that list because for the last week their clicks were less than 200.

How do I do this in power BI?

Here is the sample file:
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=C7ObDZ

[Anonymous · Accepted solution]

Hi  @slavisha84 ,

Here are the steps you can follow：

1. Create calculated table.

Table2 =
var _1=SUMMARIZE('Table','Table'[Name],
"4WeeksAge",
CALCULATE(SUM('Table'[Points]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])-3&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Points])),
"3WeeksAge",
CALCULATE(SUM('Table'[Points]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])-2&&'Table'[Name]=EARLIER('Table'[Name]))),
"2WeeksAge",
CALCULATE(SUM('Table'[Points]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])-1&&'Table'[Name]=EARLIER('Table'[Name]))),
"LastWeek",
CALCULATE(SUM('Table'[Points]),FILTER(ALL('Table'),'Table'[Week]=MAX('Table'[Week])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[Points]>=200)))
return
FILTER(_1,[LastWeek]<>BLANK())

2. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@slavisha84 , Create a separate week table, say date 

Last Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week]=max("Date"[Week])-1))

2nd  Last Week = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Week]=max("Date"[Week])-2))

Last week disp = if([Last Week] < 200 , blank(),[Last Week])

2nd  Last Week = if([Last Week] < 200 , blank(),[2nd  Last Week])

Same need to be done for other measures too