cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IWillNotLogin
Frequent Visitor

Fill 'EndDate' column with lowest 'StartDate' column

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.

IWillNotLogin_0-1623681168734.png

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? 🙂

3 REPLIES 3
daxer
Solution Sage
Solution Sage

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.

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.

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates