Calculating completion % based on expiry dates

[RichOB]

Hi, I need some help working this brain teaser out, please.  FYI **Table at bottom. Financial Year runs 1st April - 31st March**

I have a property company with 6 locations, and I conduct a variety of statutory checks. I need to be able to show 2 different calculations in 2 tables:

1

Current active figures

Current properties requiring gas certificate = 7

Has a current in-date certificate (an issue date for the next financial year) = 4 

% with current certificate = 57%

2

% of properties that obtain a gas certificate late (when the issue date of the new cert is later than the previous expiry date) 

FY 22/23 = 1 property due. 1 late = 100% Late

FY 23/24 = 2 properties due. 1 late = 50% late

FY 24/25 = 4 properties due. 2 late = 50% late

FY 25/26 = the 1st calculation can take care of the current financial year % (I think)

Property_ID	Issue_Date	Expiry Date	Type	Exp_Year
1 Lakeshore Drive	02/08/2022	02/08/2023	Gas Safety Checks	FY 23/24
1 Lakeshore Drive	12/08/2023	12/08/2024	Gas Safety Checks	FY 24/25
1 Lakeshore Drive	12/08/2024	12/08/2025	Gas Safety Checks	FY 25/26
1 Lakeshore Drive	18/08/2025	18/08/2026	Gas Safety Checks	FY 26/27
2 Logan Square	07/07/2021	07/07/2022	Gas Safety Checks	FY 22/23
2 Logan Square	10/07/2022	07/07/2023	Gas Safety Checks	FY 23/24
2 Logan Square	07/07/2023	07/07/2024	Gas Safety Checks	FY 24/25
2 Logan Square	10/07/2024	07/07/2025	Gas Safety Checks	FY 25/26
5 Giddings St	01/02/2024	01/02/2025	Gas Safety Checks	FY 24/25
6 Addison	13/06/2024	13/06/2025	Gas Safety Checks	FY 25/26
6 Addison	24/06/2025	24/06/2026	Gas Safety Checks	FY 26/27
7 Ravenswood Ave	02/08/2024	02/08/2025	Gas Safety Checks	FY 25/26
7 Ravenswood Ave	02/08/2025	02/08/2026	Gas Safety Checks	FY 26/27
1 Davies Road	01/06/2024	01/06/2025	Gas Safety Checks	FY 25/26
8 Winnemac Park	08/08/2023	08/08/2024	Gas Safety Checks	FY 24/25
8 Winnemac Park	08/08/2024	08/08/2025	Gas Safety Checks	FY 25/26

I hope this makes sense. Apologies if my calculations are off, but I hope I've portrayed what I need.

Thanks

[v-priyankata]

Hi @RichOB 
@FBergamaschi @ryan_mayu Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @RichOB 

I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @RichOB 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. if you have any other issues please reach community.

[ryan_mayu]

@RichOB 

not clear about the logic. 

Has a current in-date certificate (an issue date for the next financial year) = 4 

what is the period for next year?

and for the second one, how to define properties due?

[RichOB]

Hi @ryan_mayu ,

"Has a current in-date certificate (an issue date for the next financial year) = 4"

This is for the current financial year running 1st April - 31st March. It takes into consideration the total number of properties that have gas safety checks expiring this year, and the number of properties that have completed them so far.

What is the period for next year?

Currently in financial year 25/26, next is 26/27

and for the second one, how to define properties due?

A property is "due" when it reaches the expiry date.

If the date of the next Issue goes past the previous expiry date it is classed as being late

For example, the certification due on 01/09/2024 was on time as the next one was input the same day, and is now valid for 1 year..

1 Sacramento Dr	01/09/2023	01/09/2024	Gas Safety
1 Sacramento Dr	01/09/2024	01/09/2025	Gas Safety

In this example, the due date for the new certification was 01/09/2024 but the new one was uploaded 11/09/2024, so it's classed as being late. 

1 Sacramento Dr	01/09/2023	01/09/2024	Gas Safety
1 Sacramento Dr	11/09/2024	01/09/2025	Gas Safety

I hope this helps? Please let me know if I can give you any more info

Thanks

[ryan_mayu · Accepted solution]

@RichOB 

I think I only find 3 ID for the first one.

you can try  this

Measure = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Expiry Date]>=date(year(today())+1,4,1))),DISTINCTCOUNT('Table'[Property_ID]))

for the second one ,you can try to create a column, 

late num =

var _next=minx(FILTER('Table','Table'[Property_ID]=EARLIER('Table'[Property_ID]) && 'Table'[Issue_Date]>EARLIER('Table'[Issue_Date])),'Table'[Issue_Date])

return if (_next>'Table'[Expiry Date],1)

pls see the attachment below

[FBergamaschi]

Pleae clarify better how we can link your examples to the table you attached

It is all a mistery to me

Thanks

1

Current active figures

Current properties requiring gas certificate = 7

Has a current in-date certificate (an issue date for the next financial year) = 4 

% with current certificate = 57%

2

% of properties that obtain a gas certificate late (when the issue date of the new cert is later than the previous expiry date) 

FY 22/23 = 1 property due. 1 late = 100% Late

FY 23/24 = 2 properties due. 1 late = 50% late

FY 24/25 = 4 properties due. 2 late = 50% late

FY 25/26 = the 1st calculation can take care of the current financial year % (I think)

[RichOB]

Hi @FBergamaschi If we take the year 24/25 for example. 

1 Lakeshore Drive, due 12/08/2024

The next issue date was 12/08/2024, this means it was completed on time

2 Logan Square, due 07/07/2024

The next issue date was 10/07/2024, which was completed late as it was more than 1 day later than 07/07/2024

5 Giddings St, due 01/02/2025

Completed late as there is no next issue date

8 Winnemac Park, due 08/08/2024

The next issue date is 08/08/2024, completed on time

4 properties had gas safety checks due in 24/25

2 were late being completed = 50% of properties had late gas safety checks:

If there is more than 1 day between the expiry date and the next issue date for the same property, it is classed as being completed late.

I hope this helps, please let me know if I can give you any more info.

Thanks