undefined

[DonapatiSP_3105]

Hello everyone,

I am looking for guidance with below problem statement.

I have  data as below. My requirements is that on any calendar date, what is the status of the inspection. Consider,  first inspection date is 1-Jan-2019 and the next inspection has to be done with in 365 days

Component Name    Inspected Date    Valid for 
Component 1            1-Jan-19             365 days
Component 1            6-Jun-19            365 days
Component 1           10-Jul-20            365 days
Component 1           1-Jan-21             365 days
Component 1           1-Jul-22              365 days
Component 1           12-Aug-22          365 days

Here is the result i need to show.

To show the below status, i need to consider the inspections around that calendar date and show status. As in, until 5-Jun-19, i will consider 1-Jan-19 to calculate status. From 6th-June-19, I would need to consider 6-June-19 to calculate status

Calendar Date        Inspection Status
1-Jan-19                Inspected on time
2-Jan-19                Inspected on time
3-Jan-19                Inspected on time
4-Jan-19                Inspected on time

SO on...

In the below example, Status on each calendar date from 1-Jan-21 to 02-Jan22, Status should be 'Inspected on time'. From 03-Jan-22 to 30-June-22, It is Overdue and on 1-Jul-22 it should be Inspected past due date

Component Name     Inspected Date      Valid for 
Component 1            1-Jan-21                 365 days
Component 1            1-Jul-22                  365 days

[Icey · Accepted solution]

Hi @DonapatiSP_3105 ,

Try this:

Inspection Status =
VAR CurrentDtae_ =
    MAX ( 'Calendar'[Date] )
VAR MaxInspectedDate_ =
    CALCULATE (
        MAX ( 'Table'[Inspected Date] ),
        'Table'[Inspected Date] <= CurrentDtae_
    )
VAR MaxValidDate_ =
    IF (
        MaxInspectedDate_ <> BLANK (),
        MaxInspectedDate_ + MAX ( 'Table'[Valid for] )
    )
RETURN
    IF (
        MaxInspectedDate_ <> BLANK (),
        IF ( CurrentDtae_ <= MaxValidDate_, "Inspected on time", "Overdue" )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DonapatiSP_3105]

Thanks much @Icey , I will try this and let you know.

Appreciate your help