How to Calculate Business Days Range with Column Date - URGENT

[JLT]

Hello! 

I have a column named 'Time for Testing,' which contains a range of business days representing the required time to conclude a test, and it's in text format. Additionally, I have another column called 'Start Date,' which indicates the beginning of the test and is in date format.

How can I sum these two columns to calculate the completion date?

Thanks a lot!

[shravanreddy133 · Accepted solution]

Hi,

First, you need to convert the 'Time for Testing' column from text format to a numeric value representing the number of business days. If the 'Time for Testing' column contains values like "5-7 days" or "3-5 days", you can extract the range of business days and calculate the average. you can create a new calculated column using the following DAX formula:

TestingDays =
VAR Range = VALUE(MID([Time for Testing], 1, FIND("-", [Time for Testing]) - 1))
RETURN
IF(ISERROR(Range), VALUE([Time for Testing]), (Range + VALUE(MID([Time for Testing], FIND("-", [Time for Testing]) + 1, LEN([Time for Testing])))) / 2)

FYI, Please modify the dax according to your needs, like range and stuff.

Once you have the 'TestingDays' column, you can add it to the 'Start Date' column to calculate the completion date. Create another calculated column using the following DAX formula:

CompletionDate = [Start Date] + [TestingDays]

[Anonymous · Accepted solution]

Hi @JLT ,

Please try this way in Power Query :
Here is my sample data:

Just put all of this M function into Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jZVSEmsLFbSUTLWN7TQNzIwMlGK1UEVNzJAEjfGrl7XDEncDEm9ARZzYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Time for Testing" = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time for Testing", type text}, {"Start Date", type date}}),
    #"Split Column by Positions" = Table.SplitColumn(#"Changed Type", "Time for Testing", Splitter.SplitTextByPositions({0, 3}), {"Time for Testing.1", "Time for Testing.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Time for Testing.1", Int64.Type}, {"Time for Testing.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.AddDays([Start Date], [Time for Testing.1]))
in
    #"Added Custom"

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shravanreddy133 · Accepted solution]

Hi,

First, you need to convert the 'Time for Testing' column from text format to a numeric value representing the number of business days. If the 'Time for Testing' column contains values like "5-7 days" or "3-5 days", you can extract the range of business days and calculate the average. you can create a new calculated column using the following DAX formula:

TestingDays =
VAR Range = VALUE(MID([Time for Testing], 1, FIND("-", [Time for Testing]) - 1))
RETURN
IF(ISERROR(Range), VALUE([Time for Testing]), (Range + VALUE(MID([Time for Testing], FIND("-", [Time for Testing]) + 1, LEN([Time for Testing])))) / 2)

FYI, Please modify the dax according to your needs, like range and stuff.

Once you have the 'TestingDays' column, you can add it to the 'Start Date' column to calculate the completion date. Create another calculated column using the following DAX formula:

CompletionDate = [Start Date] + [TestingDays]