Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gauravnarchal
Post Prodigy
Post Prodigy

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

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1627437990372.png

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.

View solution in original post

8 REPLIES 8
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1627437990372.png

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
Community Support
Community Support

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:

vangzhengmsft_0-1627350295191.png

Result:

vangzhengmsft_1-1627350319466.png

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
Community Support
Community Support

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:

vangzhengmsft_0-1626766930874.png

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

ryan_mayu
Super User
Super User

@gauravnarchal 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




nvprasad
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.