How to calculate monthly duration if my date column values span over multiple months

[PBINewbi14]

Hi, I have two columns for work start and work end, and another column calculating the work duration which is the Hourly difference between these two columns, and I would like to calculate the monthly work duration. The problem is in some cases work starts in one month and ends in another month, so those duration should fall into their corresponding months. 

I created a sample table - 

Here, for index 2, dates spans over 2 months, so for the calculation, work start till the end of January will fall under January and then start of February till the work end will fall under February, same goes for index 5.

(I edited to this hourly calculation as the day calculation of Excel was creating some confusion regarding the expected result.)

Thanks in advance 🙂

[danextian]

hI @PBINewbi14

Your sample data and results are a bit unclear. In the first row, you count 4 days for January, which excludes one of the days within the start and end dates. In the second row, the first two days (29 and 30) are counted for January, but the 31st seems to be missing. If the last date is excluded, only 1 day would be counted for February in the second row. On the other hand, if the first date is excluded, then 30 and 31 should be counted for January in the second row. It seems you're excluding one day from the count, but it's unclear whether you're omitting the first or last day.

If I were to include all dates, this is what I'd get:

[PBINewbi14]

Hi, I think it's unclear probably because how excel is calculating the date difference, I only did duration = end - start, but the image you shown is basically what I wanted. Can you please let me know how you did this? Thank you.

[danextian]

You can achieve this either by using DAX with a disconnected Dates table or by materializing the date ranges in Power Query (M).

DAX Approach: This is more efficient for refresh operations, as no additional transformations are required during the data load. The logic is calculated dynamically within your visuals.

Power Query (M) Approach: This method can lead to faster visual rendering since the required calculations are precomputed during the data transformation phase. However, it might increase refresh times as the transformations are applied during the data load process.

For smaller tables, the performance difference between these approaches is negligible. The choice ultimately depends on your workflow and priorities.

Please refer to the attached pbix.

[danextian · Accepted solution]

Here it is.

[PBINewbi14]

Hi, sorry, I don't see any attachments. Thank you

[TomMartens]

Hey @PBINewbi14 ,

What is the expected result?
Do not forget to explain the expected result based on the data you provide. I'm wondering why the end date is not considered. Is this because you assume that what ever is measured is finished at end date 00:00:00 AM?

Regards,
Tom

[PBINewbi14]

Hi, thanks for the reminder, I edited the post to add an example explanation for the expected results. 

Regarding the end date time thing, the actual data I am trying to work has date-time values with hh:mm and I want to get hourly duration per month, I thought it would be simpler just to use date, and will be easier for me to explain 😄 

On excel I just did duration = end - start

Thank you