Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
3 years ago
Solved

Time Column Calculation Percentage

I have a table called Procurement_Main_Data and want to create a column that will put a yes  if the Delivered Duration is 10% greater than the Planned Duration otherwise say No   The fields Deliver...
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    because you write the measure incorrectly.
    this is how you should write it:

    10% Over =
if([Variance %]>0.1,"Yes","No
Ahmedx's avatar
Ahmedx
Super User
3 years ago

 

 

10% Over = 
VAR _limit = MINUTE([Planned Duration])*(1+0.1)
VAR _Results = if(MINUTE([Delivered Duration])>_limit,"YES","NO")
RETURN
_Results

10% Under = 
VAR _limit = MINUTE([Planned Duration])*(1-0.1)
VAR _Results = if(MINUTE([Delivered Duration])<_limit,"YES","NO")
RETURN
_Results
------------
OR
10% Over = 
VAR _limit = MINUTE([Planned Duration])*1.1
VAR _Results = if(MINUTE([Delivered Duration])>_limit,"YES","NO")
RETURN
_Results

10% Under = 
VAR _limit = MINUTE([Planned Duration])*0.9
VAR _Results = if(MINUTE([Delivered Duration])<_limit,"YES","NO")
RETURN
_Results

 

 

 

  • spandy34's avatar
    spandy34
    Responsive Resident
    3 years ago

    Ive added another column called Variance Column.  The majority of rows are fine for the 10% Under and10% Over Columns but was wondering why the records filtered in the 10% Over Column is saying 'No' when the Variance % is 1000%.  The Variance % field is Percentage format and Data Type Decimal Number

     

    Any ideas? Thank you for your ongoing support

     

     

    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago

      because you write the measure incorrectly.
      this is how you should write it:

      10% Over =
if([Variance %]>0.1,"Yes","No
      • spandy34's avatar
        spandy34
        Responsive Resident
        3 years ago

        Thank you thats done it.  Really appreciate your help as always:)

    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago
      10% Over = 
VAR _limit = 
(HOUR([Planned Duration]) *3600 + MINUTE([Planned Duration])* 60 + SECOND([PlannedDuration]))*1.1
VAR _Results = if(
       HOUR('Table'[Delivered Duration]) *3600 + MINUTE('Table'[Delivered Duration])* 60 + SECOND('Table'[Delivered Duration])
>_limit,"YES","NO")
RETURN

10% Under = 
VAR _limit = 
(HOUR([Planned Duration]) *3600 + MINUTE([Planned Duration])* 60 + SECOND([Planned Duration]))*0.9
VAR _Results = if(HOUR('Table'[Delivered Duration]) *3600 + MINUTE('Table'[Delivered Duration])* 60 + SECOND('Table'[Delivered Duration])<_limit,"YES","NO")
RETURN
_Results