Take value from latest date in PQ

[PowerBI_RD]

Hi all,

I have a question about fetching a target that changes with time. I have two tables: the first one has the StartDate for each target output. Users can input a newer date in this table and choose a different target. The second table I have in PQ is a dates tables with all dates ranging from today to 2 years back. I'm looking for a way to fetch the right target value for each of those dates.

I have a copy of the report here: https://drive.google.com/file/d/1LWLOiKrrCD37MqrERvpe2LK8Jkc869nS/view?usp=sharing

In this I take a list of dates and I check for each date if it is later than the target date. If not I remove the date, otherwise I keep it in. The next step would be to take the latest StartDate and get the target that was associated with that date. So in the screenshot below that would mean row 2 would remain and row 1 would be removed, because on June 5th the target was updated with a new StartDate.

I have tried to solve this by grouping on the Date column, but I don't know how to get the latest Target value based on the max StartDate for each Date.

I hope someone is able to help me.

Thank you.

[PowerBI_RD]

@jgeddes that's actually an elegant and simple solution. Thank you !

[jgeddes · Accepted solution]

You could merge the target query into the date query, expand the target value and then fill up to replace the null values. All dates prior to the intial target date will be left as null.