Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Date | foodstock |
01.03.2023 | 9083 |
02.03.2023 | 13045 |
03.03.2023 | |
04.03.2023 |
Any ideas on how I can solve this?
Hi @hellohelpme ,
Please try this.
foodstock = IF('table'[Date]<=TODAY(),CALCULATE(COUNT('foodsupply'[foodID]),RELATEDTABLE('foodsupply')))
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
User | Count |
---|---|
98 | |
90 | |
83 | |
69 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |