Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there,
My sample data is
desired output is :
i could achieve the earliest start and latest end. But couldn't achieve Date difference is as 6 .
the measure i tried takes entire date difference between jan 1 to dec 31 . Anybody please guide .
thank you
Hi @dejadpower ,
Thanks @JamesFR06 for the quick reply and solution. Here are some of my additions:
You can use the DATEDIFF function to calculate the number of days between measures.
Days = DATEDIFF([Earliest_Start],[Lastest_End],DAY)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-tangjie-msft , Thanks for helping.
for data:
Earliest Start : 04/01/2023
Latest End : 12/31/2023
The formula
DATEDIFF([Earliest_Start],[Lastest_End],DAY)
will give 360 days
But the actual working days of resources are 6 days.
kindly help to ahieve this
Hi @dejadpower ,
Click "transform data" to enter the power query editor to copy a table, and then open the "Advanced Editor" to copy and paste the following code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VMFTSUTKy1Dc00jcyMDIGcowN4ZxYHSSFRugKkTgoCkFyBib6BoYwhUgcDBOJUmiCodAMoTAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Start Time" = _t, #"End Time" = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Start Time.1", "Start Time.2", "Start Time.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource", type text}, {"Start Time.1", Int64.Type}, {"Start Time.2", Int64.Type}, {"Start Time.3", Int64.Type}, {"End Time", type text}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Start Time.3", type text}, {"Start Time.2", type text}, {"Start Time.1", type text}}, "en-US"),{"Start Time.3", "Start Time.2", "Start Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Start Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type date}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "End Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"End Time.1", "End Time.2", "End Time.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"End Time.1", Int64.Type}, {"End Time.2", Int64.Type}, {"End Time.3", Int64.Type}}),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"End Time.3", type text}, {"End Time.2", type text}, {"End Time.1", type text}}, "en-US"),{"End Time.3", "End Time.2", "End Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Merged", "End Time"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "end time 2", each Date.AddDays([Start Time],3)),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Time", "end time 2"}, {{"Table",each _}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Duration.Days(List.Max([Table][#"End Time"])-
List.Min([Table][#"Start Time"]))+1),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"end time 2", type date}, {"Custom", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Table"})
in
#"Removed Columns"
Then we get two tables.
Click "Close and Apply".
Then we can create measures.
Earliest_Start = CALCULATE(MIN('Table (2)'[Start Time]),ALL('Table (2)'))
Lastest_End = CALCULATE(MAX('Table (2)'[End Time]), ALL('Table (2)'))
Days = CALCULATE(SUM('Table'[Custom]),ALL('Table'))
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm getting error Cannot convert date to text in step
Split Column by Delimiter
Hi @dejadpower ,
The split step is due to the fact that I need to adjust the date formatting. If your date is correct, you just need to refer to these steps below. Click on the gear to see the step details.
1.Add [end time2] column.
2.Group rows.
3.Add a custom column.
4.Change the [Custom] column type.
5.Remove Columns.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI
Earliest_Start=calculate(min(Start Time), all(tablewithdata))
Lastest_End= calculate(max(Start Time), all(tablewithdata))
Days=[Lastest_End]-[Earliest_Start]
Thank you @JamesFR06
This method, i have already tried. it will take difference of Jan 04 to Dec 29 - which is 360 days
the expected output is 04/01 to 06/01 + 29/12 to 31/12 , which is 6 days in total
the second row difference should be skipped, because it is already covered in (29/12 to 31/12)
the 3rd , 4th , 5th rows difference should be skipped, because it is already covered in (04/01 to 06/01)
instead of taking all dates, it should cover the existing from , to date difference only.
Please guide