Forum Discussion
Calculating completion % based on expiry dates
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
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
8 Replies
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)
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
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
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
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.
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.
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.
