Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
User | Count |
---|---|
94 | |
90 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
106 | |
88 | |
64 | |
63 |