custum column if

Question

Hello everyone,&nbsp;How can i write an M code to get the must recent date between 2 columns(number 1 and number 2) and if de startdate is in 2018 and the enddate(number 3 ) is in 2019 the startdate must be 1-1-2019?&nbsp;Thanks in advance.

parry2k · Answer

Anonymous add custom column with following expression
&nbsp;
List.Max({[ColA],[ColB],[ColC]})

anonymous · Answer

I am not behind my pc right now but as i see the answer is 50% of the question. Makes me already happy.

The second part is now we got highest number that the everything with a startdate in 2018 must be 1-1-2019 if the end date is in 2019.

My goal:
I am calculating cost of 1 Year per quarter. So Every startdate in 2018 with an enddate in 2019 i have go transform to 1-1-2019. The same for start date 2019 and end date in 2020. But my guess was that i could make up the code for 2019 start date and 2020 end date from the 2018 / 2019 question.

parry2k · Answer

Anonymous I'm not very clear about your 2nd question, if you give example wiht sample data it will help.

anonymous · Answer

Ofcourse. The 3rd line in the picture shows in column the with the 1 and 2 above them that the higest date Will be in 2018. But i need dates in 2019 because the endate is in 2019 see column with the 3 above.Another Example:Startdate 29-12-2018Real start date 30-12-2018Result Will be highest date is real startdate but iT is in 2018. IT must be transformd to 1-1-2019.The same for the end dates. These are not om the picture and original question.Endate 3-2-2020Real enddate is blank/emptyHigest enddate is in 2020 but i need it to transform to 31-12-2019.End goal not for this question:At the end i Will extract the amount of days in 2019 with start date- end date and multiply them with a price.

anonymous · Answer

Some extra datesStartdate 31-12-2018Real start date 31-12-2018Enddate in 1-3-2019Result higest startdate transform to 1-1-2019 because endate in 2019.Enddate 1-3-2020Real enddate blank or nullResult startdate is in 2019 enddate transform to 31-12-2019.To calculate the costs for year 2019

Forum Discussion

custum column if

5 Replies

Related content

add a custum column (If condition error)

New Column

custum visual html content viewer embed youtube video

Grouping rows by Column and then updating Column

Adding a custum column based on an particular cell