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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Time duration/difference in same column between different status/phase

Hi, I'm trying to get the duration between the different decision types in order to visualize the total duration time per decision type, but also total duration inbetween eg. "WB1 to WB2" and "WB2 to WB3" etc.

The thing is that for each opportunity the user might jump between different decisions back and forth several times eg. from wb1 to wb2 and then back to wb1 and so on so a simple datediff will probably not work for this..

 

Any help appreciated!

 

Btw I'm connected using DirectQuery so not all functions will work with measures/calc columns.

 

 

Screenshot 2021-04-06 at 16.01.43.png

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Why the second w1 has  under o1 has 4 days and why o3 do not have any value

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak hi, 4 days refers to the duration it has been in this particular stage/decision (decision d3) before changing to the next decision d4, therefore 4 days between decision dates.

The reason for the empty cells is because I havent though of a good way of displaying those. Most likely it would be the total days duration before the entire opportunity is closed(status = won/lost)

hope it makes sense.

@Anonymous , oh, I did check that diff is small date column with the next date.

 

Try a new column 

 

new column =
var _min = minx(filter(table, [opportunity_id] = earlier([opportunity_id]) && [decisiondate] > earlier([decisiondate])),[decisiondate])
return
if(isblank(_min), blank(), datediff([decisiondate] ,_min, day))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak I tried the code however several functions are not supported (minx, filter, earlier) since the report is using DirectQuery as connection type... is there any alternative to the code above which will be supported with DQ?

Thanks.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.