Add dates based on my start and end dates & Split figures in a column into multiple rows

[paulfink]

hi guys,

i need some quick help with the above 2 problems.

Problem 1: I need to split my lines by days but i only have Start and End Dates (2 Seperate columns)

Is pretty straightforward. See desired output at bottom of post.

Problem 2: I need to split duration into multiple rows

This needs to be done in order of Problem 1 and Problem 2.

i have a column that tracks time but it is the total time and not per day which i need for a visual that has is an indepth look into particular projects.

If the figure is 3 then itll split into 3 different rows as 1

If the figure is 4.5 then itll split into 5 rows - 4 of them being 1 and the 5th being 0.5

This needs to be done in order of Problem 1 and Problem 2.

Current Data - 

Start Date	End Date	Duration
22/03/2021	26/03/2021	4.5

Desired output - 

Start Date	End Date	Duration	Day	Duration
22/03/2021	26/03/2021	4.5	22/03/2021	1
22/03/2021	26/03/2021	4.5	23/03/2021	1
22/03/2021	26/03/2021	4.5	24/03/2021	1
22/03/2021	26/03/2021	4.5	25/03/2021	1
22/03/2021	26/03/2021	4.5	26/03/2021	0.5

Does this make sense?

I can clarify if needed.

Replies will be very quick.

[Jihwan_Kim]

Sorry, I have changed my pbix file a little to look the same as your desired output.

https://www.dropbox.com/s/yxf1q0mx8l7y6ab/paulfink%202021%200325.pbix?dl=0 

[Jihwan_Kim]

Hi, @paulfink 

Please correct me if I wrongly understand your question.

I created a custom-date-table, but not connected to the main fact table, to create a table visual for your question.

If there are more tables, more rows, and more columns are involved, please share the sample data file.

Then I can look into it more accurately.

My pbix sample file's link is down below.

Day Measure =

IF (

AND (

MAX ( dates[Date] ) >= SELECTEDVALUE ( Data[Start Date] ),

MAX ( dates[Date] ) <= SELECTEDVALUE ( Data[End Date] )

),

MAX ( dates[Date] ),

BLANK ()

)

Duration =

VAR betweendays =

DATEDIFF (

SELECTEDVALUE ( Data[Start Date] ),

SELECTEDVALUE ( Data[End Date] ),

DAY

)

VAR actualduration =

SUM ( Data[Duration] )

VAR lastdayvalue = actualduration - betweendays

VAR lastday =

SELECTEDVALUE ( Data[End Date] )

RETURN

IF (

ISBLANK ( [Day Measure] ),

BLANK (),

IF ( MAX ( dates[Date] ) = lastday, lastdayvalue, COUNTROWS ( dates ) )

)

https://www.dropbox.com/s/yxf1q0mx8l7y6ab/paulfink%202021%200325.pbix?dl=0 

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

Appreciate your Kudos!!

[paulfink]

hi @Jihwan_Kim you did some good stuff thank you.

However, the Duration measure isnt giving me the 0.5

since i posted i was tested some things and realised i just jump put in my Calendar [Dates] into the table and it splits. I tried altering the Duration measure so it doesnt include the Day Measure, could you help me find a way to use my calendar dates instead of the day measure?

[Jihwan_Kim · Accepted solution]

Hi, @paulfink 

Thank you for your feedback.

please try the below. I took out DayMeasure.

Duration =

VAR betweendays =

DATEDIFF (

SELECTEDVALUE ( Data[Start Date] ),

SELECTEDVALUE ( Data[End Date] ),

DAY

)

VAR actualduration =

SUM ( Data[Duration] )

VAR lastdayvalue = actualduration - betweendays

VAR lastday =

SELECTEDVALUE ( Data[End Date] )

VAR startday = SELECTEDVALUE( Data[Start Date])

RETURN

IF (

MAX(dates[Date]) >= startday && MAX(dates[Date]) < lastday, COUNTROWS ( dates ),

IF ( MAX ( dates[Date] ) = lastday, lastdayvalue, BLANK() )

)

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

Appreciate your Kudos!!