HI guys

I am working on urgent work task trying to count the number of over new contracts, how many repeat and how many lapse.

i have the data below ( i hjave attached my workbook too)

i have linked this to a separate sheet contain daily dates so i can get the dates between and also i can use to display the dates across , i have called this months

• each account can have more that 1 contract but i want to count all the contracts  under an account as 1, using the min star date and the max end date, so in this case for account ab the contract start date would be  27/04/2017 and end date would be 27/07/2018
• contract lapse on the max end date
• repeat are the dates between the start and end date.

so for account ab the contract starts 27/04/2017  s new should be shown at FY 2016-17 , and contract ends 27/07/2018 so it should show lapsed fy 18-19

rest should be like below

please note my FY are set to july-june

i am having major problems doing this my results do not seem to be right ( checked the attached workbook ) i don't know if it is my calcultion field?

can anyone help me on this?

if this is not clear i am happy to provide any other info.

thank you guys