cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors