Forum Discussion
How to select values from a database based on dates
I have a database where each record stores the value of a project. Anytime the value of the project changes, a new record is created with a date/time stamp so that all history of changes is stored in the database. I want to be able to create a table where I select a date and the table sums up records that were on or before that date. For example, if my data looks like the table below, and I enter a date of Jan 2, 2022, the total value is $500 because the only project valid as of that date is project B, if I enter May 1, 2022, the total value is $1500 since it picks up the earlier value of Project A, if I enter any date after Jun 1, 2022, the total value is $2000 since it pickus up the latest value for project A
| Project Name | Value | Last change |
| Project A | 1000 | 1-Mar-22 |
| Project A | 1500 | 1-Jun-22 |
| Project B | 500 | 1-Jan-22 |
Would something like this work?
6 Replies
In Power query convert the Last Change field to date type
(there are lots of onlien guides on how to that)
Create a table visual with Last changes, Projcet and Value
Create a slicer for Date
Hover the mouse under the elipses three dots ....
Click the V that appaers under the elipses and select After
Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
That wont work - I need to be able to pull the last record changed before the data entered, not after. And if I just use a slicer, it will pull ALL the records prior to that date, not just the latest. For example, if I entered todays date, it would add up all three records in my sample and I only want it to add Project B and the latest entry of Project A
Would something like this work?
You are not explaining your requirments very well ☹️☹️☹️
Click here to dowload a solution
Please can you provide a copy of the input data (as table not a screen shot)
and a few examples of the desired output for different slicer dates.
Also give a crystal-clear description and ensure that fields names in your input dates, output example and description all match. so we understand what you want.
We are unpaid volunterer solvers who want to help you and it is a little frustrating when members don’t go to effort to describe the requirements really well, and then dont give kudos when we go to the effort of making suggestions. 😀
