Forum Discussion
Return a value based on a postingdate
Hello Community
I have checked some posting and I am not sure how to go about this.
I have 2 tables
Table1 has some items, amounts and a saledate
Table2 has the date ranges and the week number
Table 1
| Item | SaleDate(ddmmyyy) | Amount |
| abc | 01012020 | 83 |
| def | 18012020 | 73 |
| ghi | 19022020 | 11 |
| jkl | 18012020 | 27 |
Table 2 (date table)
| StartDate (ddmmyyy) | EndDate (ddmmyyy) | PeroidName |
| 01012020 | 07012020 | W1 |
| 08012020 | 15012020 | W1 |
| 16012020 | 23012020 | W2 |
What I need is to return the PeroidName that falls inbetween the date rate. Without calculating the sum on that day.
Thank you kindly
1 Reply
- mahoneypat
Microsoft Employee
I assume you are looking for a calculated column. You can try this expression:
PeriodName = var datethisrow = Table1[SaleDate]
return Calculate(min(Table2[PeriodName), Filter(All(Table2), Table2[StartDate] <= datethisrow && Table2[EndDate]>= datethisrow))
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
