The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
125 | |
106 | |
84 | |
64 | |
59 |
User | Count |
---|---|
257 | |
121 | |
117 | |
100 | |
79 |