Forum Discussion
Helper IHow to summarize data for last week in separate column with condition?
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
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
2 Replies
- amitchandak
Super User
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
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
