Forum Discussion

paulfink's avatar
paulfink
Icon for Post Patron rankPost Patron
5 years ago
Solved

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

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 DateEnd DateDuration
22/03/202126/03/20214.5

 

Desired output - 

Start DateEnd DateDurationDayDuration
22/03/202126/03/20214.522/03/20211
22/03/202126/03/20214.523/03/20211
22/03/202126/03/20214.524/03/20211
22/03/202126/03/20214.525/03/20211
22/03/202126/03/20214.526/03/20210.5

 

Does this make sense?

 

I can clarify if needed.

 

Replies will be very quick.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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!!

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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 ) )
    )
     
     
    Did I answer your question? Mark my post as a solution!
    Appreciate your Kudos!!

     

    • paulfink's avatar
      paulfink
      Icon for Post Patron rankPost Patron
      5 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        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!!