How to calculate continuous dates

ClemFandango — Fri, 05 May 2023 08:32:42 GMT

Good morning all,

I have an ongoing topic to find gaps in continuous dates, but this is a slightly different problem.

For this topic, I am trying to find continuous dates for each ref. Please see example of the data and the columns 'Continuous Start' & 'Continuous End' that I am trying to create. The main difficulty is that some of the start and end dates overlap.

If possible, I am also trying to create another column called 'Rank'. The 'Rank' would be an auto generated number that ranks the continuous end dates in decending order i.e the highest rank number would be the MAX continuous end date. There may be about 10+ ranks.

Any help eternally appreciated:-

(apologies, I have done both a screenshot and table, because the formatting never works on the table)

Ref	Start Date	End Date	Continuous Start	Continuous End	Rank
127	01/01/2018	31/03/2019	01//01/2018	31/03/2020	1
127	01/01/2018	31/03/2019	01//01/2018	31/03/2020	1
127	01/01/2018	31/03/2019	01//01/2018	31/03/2020	1
127	01/01/2018	31/03/2019	01//01/2018	31/03/2020	1
127	01/01/2018	31/03/2019	01//01/2018	31/03/2020	1
127	31/03/2019	31/03/2020	01//01/2018	31/03/2020	1
127	31/03/2019	31/03/2020	01//01/2018	31/03/2020	1
127	31/03/2019	31/03/2020	01//01/2018	31/03/2020	1
127	30/07/2020	01/09/2021	30/07/2020	31/03/2023	2
127	12/08/2020	01/04/2021	30/07/2020	31/03/2023	2
127	31/03/2021	31/03/2022	30/07/2020	31/03/2023	2
127	31/08/2021	30/11/2022	30/07/2020	31/03/2023	2
127	30/11/2021	07/12/2021	30/07/2020	31/03/2023	2
127	31/03/2022	31/03/2023	30/07/2020	31/03/2023	2
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	01/01/2018	29/09/2019	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	30/09/2019	30/09/2020	01/01/2018	30/09/2022	1
442	21/07/2020	30/08/2021	01/01/2018	30/09/2022	1
442	30/09/2020	30/09/2021	01/01/2018	30/09/2022	1
442	30/09/2020	30/09/2021	01/01/2018	30/09/2022	1
442	30/09/2020	30/09/2021	01/01/2018	30/09/2022	1
442	30/09/2020	30/09/2021	01/01/2018	30/09/2022	1
442	02/10/2020	30/09/2021	01/01/2018	30/09/2022	1
442	31/08/2021	31/08/2022	01/01/2018	30/09/2022	1
442	30/09/2021	30/09/2022	01/01/2018	30/09/2022	1
442	30/09/2021	30/09/2022	01/01/2018	30/09/2022	1
442	30/09/2021	30/09/2022	01/01/2018	30/09/2022	1
442	30/09/2021	30/09/2022	01/01/2018	30/09/2022	1
442	30/09/2021	30/09/2022	01/01/2018	30/09/2022	1
442	11/10/2021	13/10/2021	01/01/2018	30/09/2022	1

Re: How to calculate continuous dates

Mahesh0016 — Fri, 05 May 2023 11:17:06 GMT

ClemFandango Please Share your expected output.

Re: How to calculate continuous dates

ClemFandango — Fri, 05 May 2023 12:26:14 GMT

Hi Mahesh0016

The calculated columns 'Continuous Start', 'Continuous End' and 'EndRank' are showing the expected/desired output at this stage. By creating a continuous start date & continuous end date I will be able to identify the date when a 'Ref' is 'New', 'Lost' or 'Returned' and this will be calculated within a date table

topic How to calculate continuous dates in DAX Commands and Tips

How to calculate continuous dates

Re: How to calculate continuous dates

Re: How to calculate continuous dates