topic Break Duration Down Across Multiple Months in DAX Commands and Tips

Break Duration Down Across Multiple Months

jakoob — Wed, 26 Apr 2023 18:16:02 GMT

Hi,

I have a potentially complex problem I was hoping to get some help or guidance with. I have some data that looks similar to this:

Start Time	End Time
3/31/2023 11:40pm	4/1/2023 12:40am
3/31/2023 11:40pm	3/31/2023 11:45pm
4/4/2023 12:40am	4/4/2023 1:00am
2/4/2023 12:40am	4/4/2023 12:40am

I need to be able to calculate the duration of time between Start Time and End Time, however I need to break down this duration across multiple months. I can easily find total duration time by simply creating a calculated column and using DATEDIFF with MINUTES interval, however I need to be able to break down this duration total down by month in my report visuals.

For example, for the very last row in my example: I need to be able to chart [x] amount of mins in February, [x] amount of mins in March, and [x] amount of mins in April.

Is this even possible with DAX? Any advise would be greatly appreciated.

Re: Break Duration Down Across Multiple Months

Anonymous — Fri, 28 Apr 2023 02:47:51 GMT

Hi jakoob ,

"I need to be able to chart [x] amount of mins in February, [x] amount of mins in March, and [x] amount of mins in April."

What is the last row output?Like the following?

start time	end time	2	3	4
2/4/2023 12:40am	4/4/2023 12:40am	hours	hours	hours

What form does the whole table take?

As far as I know there is no directly implementable dax, it can be implemented in PQ, but it includes time and seems to be more complicated.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Re: Break Duration Down Across Multiple Months

jakoob — Tue, 02 May 2023 19:13:03 GMT

Sorry, maybe my explanation was not clear enough:

Lets use the row you provided in your reply

start time	end time
2/4/2023 12:40am	4/4/2023 12:40am

The total difference in time between the start time and end time above is 84,958 minutes.

However this is not what I need. Of the 84,960 total minutes between the two dates above, I need to break down the mins by each month. This particular event occurred during the months of February, March, and April. During each of those months, this is how many mins this event spanned across those 3 months.

February: 35,959 mins

March: 44,639 mins
April: 4,360 mins

Total Time: 84,958 mins

I need to be able to graph this in a visual