Forum Discussion
Solved
Offset a Column based on another column
My data is as follows:
| Week | Name | Emails Sent |
| 38 | Person 1 | 134 |
| 38 | Person 2 | 237 |
| 38 | Person 3 | 236 |
| 37 | Person 1 | 127 |
| 37 | Person 2 | 345 |
| 37 | Person 3 | 198 |
| etc | etc | etc |
And I'm looking to add the 'Last Week Emails Sent' column. In this column I want to take 'Emails Sent' and offset it up by 1 week.
| Week | Name | Emails Sent | Last Week Emails Sent |
| 38 | Person 1 | 134 | 127 |
| 38 | Person 2 | 237 | 345 |
| 38 | Person 3 | 236 | 198 |
| 37 | Person 1 | 127 | 107 |
| 37 | Person 2 | 345 | 234 |
| 37 | Person 3 | 198 | 175 |
| etc | etc | etc | etc |
How would I go about this?
Thanks
- You should be able to add a new column:
Last Week Emails Sent =
MAXX(FILTER(ALL(Table), Table[Name] = EARLIER(Table[Name]) && Table[Week] -1 = EARLER(Table[Week]) ), Table[Emails Sent] )
You may also be able to get what you want using measures, but don't know what your ultimate goal is. Hi morgancampbell ,
You can use the following calculated column:
Last Week Emails Sent = CALCULATE(SUM('Table'[Emails Sent]),FILTER('Table','Table'[Week] = EARLIER('Table'[Week])-1&&'Table'[Name] = EARLIER('Table'[Name])))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
2 Replies
- You should be able to add a new column:
Last Week Emails Sent =
MAXX(FILTER(ALL(Table), Table[Name] = EARLIER(Table[Name]) && Table[Week] -1 = EARLER(Table[Week]) ), Table[Emails Sent] )
You may also be able to get what you want using measures, but don't know what your ultimate goal is. Hi morgancampbell ,
You can use the following calculated column:
Last Week Emails Sent = CALCULATE(SUM('Table'[Emails Sent]),FILTER('Table','Table'[Week] = EARLIER('Table'[Week])-1&&'Table'[Name] = EARLIER('Table'[Name])))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai