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)

 

Return

 

(Days) If the Serialnumber starts with other than 9  Subtract – (Days) Serialnumber starts with 9

 

Data

 

Table 1

SerialIDSerialNumber
894482793054
896868795251
903783800600
910030905390
910802906090
912014907243
912764808136
912763908178

 

Table 2

 

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

 

Thanks

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.

8 Replies

  • nvprasad's avatar
    nvprasad
    Solution Sage
    5 years ago

    Hi,

    Could you please check the below DAX ( as per my understanding)

     

    NewColumn =
    Var Days =('Table'[returndate]-'Table'[Shipdate])*'Table'[NumberOfItems]

    Return   IF(CONVERT(LEFT('Table'[SerialID],1),INTEGER)= 9, 9-Days,Days)
     

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

    • 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?

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, gauravnarchal 

     

    Try to create a metric like this:

    New = 
var Days=DATEDIFF('Table2'[returndate],'Table2'[Shipdate],DAY)*'Table2'[NumberOfItems]
var _startOfNum=CONVERT(LEFT(RELATED(Table1[SerialNumber]),1),INTEGER)
var _if=IF(_startOfNum=9,"Y","N")
return _if

    The return value mentioned above is not very clear to me, so I use "yes" and "no" to show the result, please replace it with the value you want, if you are not very clear about the result of the replacement then please describe your return condition in more detail.

     

    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.

     

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      5 years ago

      v-angzheng-msft  - I am getting an error "Cannot convert value 'ERDEFAULT' of type Text to type Integer."

       

      One field value in the column had the text "ERDEFAULT"  how can I return this as an error?

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, gauravnarchal 

     

    You still have non-numeric text in the serialnumber column, right? If you use my formula, then the error you will see should be that you cannot convert "E" to an integer instead of "ERDEFAULT", because I extracted the left one character. If there is non-numeric text here, then add a judgment condition and the adjusted formula could look like this:

    New =
VAR Days =
    DATEDIFF ( 'Table2'[returndate], 'Table2'[Shipdate], DAY ) * 'Table2'[NumberOfItems]
VAR _extract1 =
    CONVERT ( LEFT ( RELATED ( Table1[SerialNumber] ), 1 ), INTEGER )
VAR _startOfNum =
    IF (
        ISERROR ( CONVERT ( LEFT ( RELATED ( Table1[SerialNumber] ), 1 ), INTEGER ) ),
        BLANK (),
        _extract1
    )
VAR _if =
    IF ( _startOfNum = 9, "Y", "N" )
RETURN
    _if

    Sample data:

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    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.