Hello! I have a data table that logs food in a store. All food types has an expiry date, and the original table include both food that is expired and food that is still good. The dataset is refreshed every day, which means that new items of food can come in and food can expire.
I want to create a table that collects the count of food that is not expired every day from today. It does not have to go back in time, but if I could start collecting the counts today (or one of the following days), I would soon get a good overview of how the stock of non-expired food develops over time.
I have tried to create a calendar-table and add the following column:
foodstock=if(table[Date]=TODAY(), count(foodsupply[foodID]))
This collects the count for todays date, but the next day it "overwrites" the day before to blank:
Example:
On March 1st it gives me this:
| Date | foodstock |
| 01.03.2023 | 9083 |
| 02.03.2023 | |
| 03.03.2023 | |
| 04.03.2023 | |
On March 2nd it gives me this:
| Date | foodstock |
| 01.03.2023 | |
| 02.03.2023 | 13045 |
| 03.03.2023 | |
| 04.03.2023 | |
I wish it could give me this:
| Date | foodstock |
| 01.03.2023 | 9083 |
| 02.03.2023 | 13045 |
| 03.03.2023 | |
| 04.03.2023 | |
Any ideas on how I can solve this?
