undefined

Question

Hello everyone,&nbsp;I am looking for guidance with below problem statement.&nbsp;I have&nbsp; data as below. My requirements is that on any calendar date, what is the status of the inspection. Consider,&nbsp; first inspection date is 1-Jan-2019 and the next inspection has to be done with in 365 days&nbsp;Component Name&nbsp; &nbsp; Inspected Date&nbsp; &nbsp; Valid for&nbsp;Component 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1-Jan-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6-Jun-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10-Jul-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1-Jan-21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1-Jul-22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12-Aug-22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 365 daysHere is the result i need to show.&nbsp;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&nbsp;Calendar Date&nbsp; &nbsp; &nbsp; &nbsp; Inspection Status1-Jan-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Inspected on time2-Jan-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Inspected on time3-Jan-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Inspected on time4-Jan-19&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Inspected on timeSO on...&nbsp;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&nbsp;Component Name&nbsp; &nbsp; &nbsp;Inspected Date&nbsp; &nbsp; &nbsp; Valid for&nbsp;Component 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1-Jan-21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;365 daysComponent 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1-Jul-22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 365 days&nbsp;

icey · Accepted Answer

Hi&nbsp;DonapatiSP_3105&nbsp;,
&nbsp;
Try this:
Inspection Status =
VAR CurrentDtae_ =
    MAX ( 'Calendar'[Date] )
VAR MaxInspectedDate_ =
    CALCULATE (
        MAX ( 'Table'[Inspected Date] ),
        'Table'[Inspected Date] &lt;= CurrentDtae_
    )
VAR MaxValidDate_ =
    IF (
        MaxInspectedDate_ &lt;&gt; BLANK (),
        MaxInspectedDate_ + MAX ( 'Table'[Valid for] )
    )
RETURN
    IF (
        MaxInspectedDate_ &lt;&gt; BLANK (),
        IF ( CurrentDtae_ &lt;= MaxValidDate_, "Inspected on time", "Overdue" )
    )

&nbsp;
&nbsp;
Best Regards,
Icey
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

undefined

2 Replies

Related content

undefined

undefined

undefined

undefined

undefined