Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jakoob
Frequent Visitor

Break Duration Down Across Multiple Months

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 TimeEnd Time
3/31/2023 11:40pm4/1/2023 12:40am
3/31/2023 11:40pm3/31/2023 11:45pm
4/4/2023 12:40am4/4/2023 1:00am
2/4/2023 12:40am4/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.

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

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.

Sorry, maybe my explanation was not clear enough:

 

Lets use the row you provided in your reply

start timeend time
2/4/2023 12:40am4/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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.