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 

 

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.