Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
SerialID | SerialNumber |
894482 | 793054 |
896868 | 795251 |
903783 | 800600 |
910030 | 905390 |
910802 | 906090 |
912014 | 907243 |
912764 | 808136 |
912763 | 908178 |
Table 2
SerialID | Shipdate | returndate | NumberOfItems |
894482 | 10-Jan-21 | 30-Jan-21 | 1 |
896868 | 3-Feb-21 | 5-Feb-21 | 2 |
903783 | 27-Mar-21 | 11-May-21 | 1 |
910030 | 30-Apr-21 | 29-May-21 | 1 |
910802 | 29-May-21 | 29-May-21 | 1 |
912014 | 30-May-21 | 29-Jun-21 | 5 |
912764 | 6-Jun-21 | 12-Jun-21 | 2 |
912763 | 6-Jun-21 | 12-Jun-21 | 6 |
Thanks
Solved! Go to Solution.
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.
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.
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.
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.
@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?
not clear about your request. What's the expected output?
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
SerialID | SerialNumber |
894482 | 793054 |
896868 | 795251 |
903783 | 800600 |
910030 | 905390 |
910802 | 906090 |
912014 | 907243 |
912764 | 808136 |
912763 | 908178 |
915263 | ERDEFAULT |
917763 | 90LKTLRW |
920263 | LIU09PLM |
922763 | "NJK9800 |
Table2
SerialID | Shipdate | returndate | NumberOfItems |
894482 | 10-Jan-21 | 30-Jan-21 | 1 |
896868 | 3-Feb-21 | 5-Feb-21 | 2 |
903783 | 27-Mar-21 | 11-May-21 | 1 |
910030 | 30-Apr-21 | 29-May-21 | 1 |
910802 | 29-May-21 | 29-May-21 | 1 |
912014 | 30-May-21 | 29-Jun-21 | 5 |
912764 | 6-Jun-21 | 12-Jun-21 | 2 |
912763 | 6-Jun-21 | 12-Jun-21 | 6 |
915263 | 11-Jun-21 | 17-Jun-21 | 1 |
917763 | 16-Jun-21 | 22-Jun-21 | 1 |
920263 | 21-Jun-21 | 27-Jun-21 | 5 |
922763 | 26-Jun-21 | 2-Jul-21 | 1 |
RESULT
Table 2 & Visual should be as below
SerialID | Shipdate | returndate | NumberOfItems | Days (Result) | SerialNumber |
894482 | 10-Jan-21 | 30-Jan-21 | 1 | 20 | 793054 |
896868 | 3-Feb-21 | 5-Feb-21 | 2 | 2 | 795251 |
903783 | 27-Mar-21 | 11-May-21 | 1 | 45 | 800600 |
910030 | 30-Apr-21 | 29-May-21 | 1 | -29 | 905390 |
910802 | 29-May-21 | 29-May-21 | 1 | 0 | 906090 |
912014 | 30-May-21 | 29-Jun-21 | 5 | -30 | 907243 |
912764 | 6-Jun-21 | 12-Jun-21 | 2 | 6 | 808136 |
912763 | 6-Jun-21 | 12-Jun-21 | 6 | -6 | 908178 |
915263 | 11-Jun-21 | 17-Jun-21 | 1 | Error | ERDEFAULT |
917763 | 16-Jun-21 | 22-Jun-21 | 1 | -6 | 90LKTLRW |
920263 | 21-Jun-21 | 27-Jun-21 | 5 | Error | LIU09PLM |
922763 | 26-Jun-21 | 2-Jul-21 | 1 | Error | "NJK9800 |
Total | 2 |
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?
Proud to be a Super User!
Hi,
Could you please check the below DAX ( as per my understanding)
Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |