Start Date & End Date in additional agreements

[Anonymous]

Hi everyone,

I have a column of agreements like this:

And should to combine it to this form:

The logic is simple: additional agreements were concluded during the period of the main agreement (1, 2). The rate was changed.

And in a final, I have to get the table like this one:

Does anyone have an idea how to move forward from here?

Many thanks,

Konstantin

*Data first picture:

name_client	date_start	date_end	rate	agreement
mr_x	01.10.2017	30.09.2019	3200	1
mr_x	01.12.2017	30.09.2019	3400	1
mr_x	01.01.2018	30.09.2019	3400	1
mr_x	01.12.2018	30.09.2019	3400	1
mr_x	01.01.2019	30.09.2019	3600	1
mrs_y	29.03.2019	30.09.2019	3600	2
mrs_y	01.10.2019	30.09.2024	3600	2

[amitchandak]

@ImkeF , can you help on this

[ImkeF]

Hi @Anonymous ,

you can use this function to get the value from the next column and subtract a day to get the end date as a first step:

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

(use the group-parameters in there)

After that you can use this function to parse out the days: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/

[Anonymous]

Hi @ImkeF 

thanks for your help

trying to solve it.

Please, close this tread - dublicated it by mistaken.

Continuation of the topic: https://community.powerbi.com/t5/Power-Query/Start-date-amp-end-date-in-custom-column-by-day/td-p/12...