Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
5 years ago
Solved

Need help with Calculated Column

Hello All   I need your help to create a calculated column as per below   Days = Calculate Datediff between Shipdate and returndate (Day)  Multiply (*) with number of items (NumberOfItems)   Re...
Need Help
Show and Tell
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, gauravnarchal 

     

    When there is a description of the desired output and requirements and condition judgments, then the solution can be obtained earlier.

     

    try to create a measure like this:

     

    _Result = 
var _is9=DATEDIFF('Table2'[returndate],'Table2'[Shipdate],DAY)*'Table2'[NumberOfItems]
var _not9=DATEDIFF('Table2'[Shipdate],'Table2'[returndate],DAY)*'Table2'[NumberOfItems]
var _extract1=CONVERT(LEFT(RELATED(Table1[SerialNumber]),1),INTEGER)
var _nonconverted=ISERROR(CONVERT(LEFT(RELATED(Table1[SerialNumber]),1),INTEGER))
var _startOfNum=IF(_nonconverted,"Error",IF(_extract1=9,FORMAT(_is9,"General Number"),FORMAT(_not9,"General Number")))
return _startOfNum

     

    Result:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

gauravnarchal 

not clear about your request. What's the expected output?

  • gauravnarchal's avatar
    gauravnarchal
    Post Prodigy
    5 years ago

    hI ryan_mayu 

     

    Requirement

     

    If the Serialnumber starts with 9   then

    Calculate Datediff between returndate (Substract [-]) Shipdate and then Multiply (*) with number of items (NumberOfItems)

    else

    Calculate Datediff between Shipdate (Substract [-]) returndate and then Multiply (*) with number of items (NumberOfItems)

    else

    return "Error" (For any nonconverted values)

     

    DATA

     

    Table1

     

    SerialIDSerialNumber
    894482793054
    896868795251
    903783800600
    910030905390
    910802906090
    912014907243
    912764808136
    912763908178
    915263ERDEFAULT
    91776390LKTLRW
    920263LIU09PLM
    922763"NJK9800

     

    Table2

     

    SerialIDShipdatereturndateNumberOfItems
    89448210-Jan-2130-Jan-211
    8968683-Feb-215-Feb-212
    90378327-Mar-2111-May-211
    91003030-Apr-2129-May-211
    91080229-May-2129-May-211
    91201430-May-2129-Jun-215
    9127646-Jun-2112-Jun-212
    9127636-Jun-2112-Jun-216
    91526311-Jun-2117-Jun-211
    91776316-Jun-2122-Jun-211
    92026321-Jun-2127-Jun-215
    92276326-Jun-212-Jul-211

     

     

    RESULT

     

    Table 2  & Visual should be as below

    SerialIDShipdatereturndateNumberOfItemsDays (Result)SerialNumber
    89448210-Jan-2130-Jan-21120793054
    8968683-Feb-215-Feb-2122795251
    90378327-Mar-2111-May-21145800600
    91003030-Apr-2129-May-211-29905390
    91080229-May-2129-May-2110906090
    91201430-May-2129-Jun-215-30907243
    9127646-Jun-2112-Jun-2126808136
    9127636-Jun-2112-Jun-216-6908178
    91526311-Jun-2117-Jun-211ErrorERDEFAULT
    91776316-Jun-2122-Jun-211-690LKTLRW
    92026321-Jun-2127-Jun-215ErrorLIU09PLM
    92276326-Jun-212-Jul-211Error"NJK9800
       Total2 

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      5 years ago

      gauravnarchal 

      Measure = if(LEFT(max(Table1[SerialNumber]),1)="9",int((max(Table2[Shipdate])-max(Table2[returndate]))),int(max(Table2[returndate])-max(Table2[Shipdate])))

      pls see the attachment below.

       

      Now the question is how to identify "nonconverted values"? Any logic for this?