Forum Discussion

ShaneL1979's avatar
ShaneL1979
New Member
3 years ago

Separating Duration by Month

Hello Everyone,

 

The issue I am currently dealing with is calculating duration within a given month. For most items this is pretty straight forward where (end date/time) - (start date/time) since most items start/end in the same month. However, occasionally the event starts in one month and ends in the next. Since my current charts are looking at start dates on what to include, this results in the start month receiving the entirety of the duration and the end month receiving none. 

 

Example:

 

START DATE/TIME = 11/30/2022 12:00

END DATE/TIME = 12/1/2022 06:00

 

November report shows duration of 18 hours for this event. December report shows duration of 0 hours for this event.

 

November report should show 12 hours for this event. December report should show 6 hours for this event.

 

Hopefully this was clear. Please let me know if there are any questions, or if you know a solution. If it wasn't obvious, I am fairly new to PBI so if at all possible, nothing too complicated to implement.

 

Thank you.

 

 

 

 

Need Help

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi ShaneL1979 

    Thanks for reaching out to us.

    >> 

    November report shows duration of 18 hours for this event. December report shows duration of 0 hours for this event

    November report should show 12 hours for this event. December report should show 6 hours for this event.

    please share some sample data and expected output in table format.

     

     

     

    Best Regards,

    Community Support Team _Tang

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

    • ShaneL79's avatar
      ShaneL79
      Helper I
      3 years ago

      Thanks for responding. Hopefully the table below will help clarify what I am looking for:

       

      IDWORK START TIMEWORK END TIME
      11/31/2022 04:002/1/2022 14:00
      22/1/2022 12:002/4/2022 12:00
      31/31/2022 00:003/1/2022 12:00
      411/30/2022 12:00 

       

      For ID #1 the hours should be distributed as follows:

      • January (20 hours)
      • February (14 hours)

       

      For ID #2 the hours should be distributed as follows:

      • February (72 hours)

       

      For ID #3 the hours should be distributed as follows:

      • January (24 hours)
      • February (672 hours) 
      • March (12 hours)

       

      For ID #4 the hours should be distributed as follows:

      • November (12 hours)
      • December (448 hours)

      At the time of writing this comment, we are 448 hours into December and the work is still not completed so there is no WORK END TIME value. However, November has ended so we can enter the 12 hours from this work into the November report. Ideally the current month value would just update as per the refresh schedule until WORK END TIME was entered, or we go into the next month.

       

      Please let me know if that isn't clear. Thank you.