@Fowmy
I'm curious if there is a way to modify the code below. I used it to extract the data from the Sales column for the current date into a new column, thanks to @Fowmy but the problem is that it enters 0 for the current date even when i know there are values for the current date. I don't want to have to enter the dates manually, i want them autogenerated. Thanks so much for helping.
= Table.AddColumn(#"Extract Week Before Previous Week", "Today", each if [TransactionDate] = DateTime.FixedLocalNow() then [Sales] else 0)
| | | |
| TransactionDate | Product | Sales |
| 7/17/2020 : 5:30:13 PM | Beans | 1 |
| 7/17/2020 : 7:45:29 AM | Beans | 0 |
| 7/3/2020 : 7:37:44 PM | Rice | 0 |
| 7/17/2020 : 5:37:44 AM | Fruit | 3 |
| 7/16/2020 : 7:37:44 AM | Rice | 0 |
| 7/5/2020 : 10:00:44 AM | Yam | 4 |
| 7/17/2020 : 04:37:19 PM | Fruit | 3 |
| 7/16/2020 : 11:37:14 PM | Fruit | 3 |
| 7/1/2020 : 5:37:13 AM | Yam | 1 |
| 7/15/2020 : 7:37:44 PM | Fruit | 0 |
| 7/4/2020 : 2:02:12 PM | Beans | 1 |
| 7/5/2020 : 7:37:44 AM | Beans | 0 |
| 7/16/2020 : 7:37:44 PM | Rice | 2 |
| 7/17/2020 : 5:27:09 PM | Beans | 0 |
| 7/16/2020 : 7:37:44 PM | Rice | 0 |
| 7/26/2020 : 6:20:11AM | Rice | 3 |
| 7/26/2020 : 7:37:44 PM | Rice | 4 |
| 7/16/2020 : 12:37:44 PM | Fruit | 8 |
| 7/16/2020 : 9:37:40 AM | Fruit | 9 |
way to calculate the Today column such that it doesn't show zero at all times for the current day even when it's not ended. For example, I have in my data that there's been 2 items sold for today but regardless it still shows 0 for all the data in that column. Currently this is the formula in power query that I used according to your solution. Thanks.
= Table.AddColumn(#"Extract Week Before Previous Week", "Today", each if [Date] = DateTime.FixedLocalNow() then [Sold] else 0)
