DAX: New Table that splits time tracking into single lines

[paulfink]

Hi guys,

I need a new table that splits the lines of my time tracking table that tracks time of sickness and holiday.

In my time tracking table it is structured as so:

• Name
• Type (Holiday or Sickness)
• Start Date
• End Date
• Duration

I need the lines to be split out depending on the duration so if the duration is 2 it will have 2 lines that are both 1 or if the duration is 3.5 it will be split out in 4 lines, 3 lines that are 1 and 1 line that is 0.5.

Name	Start Date	End Date	Duration
John S	08/12/2020	11/12/2020	1
John S	08/12/2020	11/12/2020	1
John S	08/12/2020	11/12/2020	1
John S	08/12/2020	11/12/2020	0.5

This is how i want the data to come out.

We currently have this setup so it counts the days from Start Date to End Date but the duration stays the same for all lines. Current Data is below.

Name	Start Date	End Date	Duration
John S	08/12/2020	11/12/2020	3.5
John S	08/12/2020	11/12/2020	3.5
John S	08/12/2020	11/12/2020	3.5
John S	08/12/2020	11/12/2020	3.5

We used a CROSSJOIN formula with the time tracking table and the calendar table:

Time Tracking Combined = 
FILTER(CROSSJOIN('Time Tracking','Calendar'),'Calendar'[Date]>='Time Tracking'[Start].[Date]&&'Calendar'[Date]<='Time Tracking'[End].[Date])

What do i have to change so that the duration is split??

[lbendlin]

Does it have to be DAX?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIVtJRMjTSt9A3MjAyALKN9UyVYnWgkk7okkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name " = _t, #"Start Date" = _t, Duration = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Duration", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Dates([Start Date],Number.RoundUp([Duration]),#duration(1, 0, 0, 0))),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Grouped Rows" = Table.Group(#"Expanded Date", {"Name "}, {{"Count", each _, type table [#"Name "=nullable text, Start Date=nullable date, Duration=nullable number, Date=date]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name ", "Start Date", "Duration", "Date", "Index"}, {"Name ", "Start Date", "Duration", "Date", "Index"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Value", each if [Index] <= [Duration] then 1 else [Duration]+1-[Index]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom2",{"Name ", "Date", "Value"})
in
#"Removed Other Columns1"

[paulfink]

This didnt help, it just replicates the tables i made in my post. I need to combine 2 tables in my power bi report and have the lines split if the duration is more than 1

[lbendlin]

Did you have problems running my code? It produces your expected outcome.

[paulfink]

Yes it did work but i do not know how to make it applicable for my data

[v-xulin-mstf · Accepted solution]

Hi @paulfink,

If you want to use Dax, you can try this demo.

Here is the output:

Best Regard,

Link 

[paulfink]

Sorry for the late reply @v-xulin-mstf 

the picture of the table you attached is not my desired output.

The Deminsion has to add up to the duration.

So if John B's duration is 3, then he would get 3 seperate lines and in those lines, the dimension would be 1.

and if the Duration is greater than 1 and also has a 0.5 decimal, it should have an additonal line that is 0.5.

[v-xulin-mstf]

Hi @paulfink

I got you, but it is not supported with dax now, the demo I provided is a workaround.

Best Regards,

Link

[paulfink]

glad you understand.

how are we going to go about this because ive been stuck