Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

undefined

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, f...

Reports

Icey

4 years ago

Hi Anonymous ,

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.

undefined.pbix66 KB

Icey

Community Support

4 years ago

Hi Anonymous ,

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.

undefined.pbix66 KB

Anonymous

Not applicable

4 years ago

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

Appreciate your help