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 

 

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?