Fill 'EndDate' column with lowest 'StartDate' column

[IWillNotLogin]

Hello,

Been trying to find a solution to this, but to no avail.

Some context:

I have a table which contains a certain bid amount and how long that bid is/was valid for (StartDate/EndDate).

Unfortunately some of those bids do not contain an EndDate and so the goal here is to find another bid for the same "job" and fill its EndDate wit hthe latter's bid StartDate.

And so to attempt this, I created a column called 'EndDate_Cleansed' and used the formula before.

EndDate_Cleansed = IF(JobMediaBids[EndDate]=BLANK(),
CALCULATE(MIN(JobMediaBids[StartDate]), FILTER(JobMediaBids,JobMediaBids[StartDate]>[StartDate] && JobMediaBids[MediaId]=[MediaId] && JobMediaBids[CampaignId]=[CampaignId] && JobMediaBids[JobId]=[JobId])),
JobMediaBids[EndDate])

This unfortunately does not seem to work as per screenshot above.

Thoughts? 🙂

[Anonymous]

This should be calculated in Power Query, not in DAX. One reason for this is that it's easier in Power Query, the second being that you should never create DAX-calculated columns in fact tables. If you ignore this rule, you'll be sorry rather sooner than later.

[IWillNotLogin]

Thank you for taking the time to respond. Not very familiar with PowerBI so didn't think of that.

I'll give that a go.

[Anonymous]

In Power Query it may be as easy as sorting the data in the right order, grabbing the start date from the record above (with some logic regarding the bid number, this can be done in a number of ways, one of them being JOINS) and then creating a new column with a simple formula and lastly, using the Fill Down feature.