How to select values from a database based on dates

[jal1aaa]

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

[speedramps]

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. 😀

[speedramps]

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 !

[jal1aaa]

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

[NickolajJessen · Accepted solution]

Would something like this work?

[jal1aaa]

I think that works, thanks for the quick response.  One more question.  I actually have one more variable on my projects table on whether the project update is approved or not.  I would like to exclude project updates that are not approved.  For example if my data included approval and I selected today as a date, it would not use the Jun 22 update for Project A since it was not approved.  Can I add a condition of Approved = "Y" into the measure you created

Project Name	Value	Last change	Approved
Project A	1000	1-Mar-22	Y
Project A	1500	1-Jun-22	N
Project B	500	1-Jan-22	Y

[NickolajJessen]