Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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
     
     
ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

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's avatar
RichOB
Post Partisan
1 year ago

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 Dr01/09/202301/09/2024Gas Safety
1 Sacramento Dr01/09/202401/09/2025Gas 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 Dr01/09/202301/09/2024Gas Safety
1 Sacramento Dr11/09/202401/09/2025Gas Safety

 

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

 

Thanks

 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    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