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.

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.