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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bhattg
Frequent Visitor

Calculating days between Finish Time to Finish Time

Hello,

I have the following data and trying to calculate days between Finish Dates, the desired output is highlighted in yellow in the second pic below:     

 

First Pic: Data Layout

 

        Data LayoutData Layout

 

Second Pic: Desired output

 

Expected.png

I need help with writing a DAX expression that would calculated date difference between each row sequentially.

 

 

6 REPLIES 6
johnyip
Super User
Super User

Hi @bhattg ,

 

I observed that the [FINISH DATE] in your sample data is "sorted naturally" ascendingly. Please try using this DAX, it involves using the window function that PowerBI has introduced recently, which also takes me some time to practice:

 

Days Between = 
VAR Data = 
PATHITEM(CONCATENATEX(OFFSET(1,ORDERBY('Table (2)'[FINISH DATE],ASC)),[FINISH DATE],"|"),1)
VAR Is_Blank = Data=BLANK()
RETURN IF(Is_Blank,"NULL",ABS(VALUE(VALUE(Data)-MAX('Table (2)'[FINISH DATE]))))

 

 

johnyip_0-1676349915694.png

 

VERY IMPORTANTLY, please ensure your table vis has sorted [FINISH DATE] correctly (ascendingly as per [FINISH DATE]) as shown, unless you don't mind at all about the display order.

 

For an alternative DAX, inspired by your reply, you can use DATEDIFF() instead of my verison, which is:

 

 

Days Between = 
VAR Data = 
PATHITEM(CONCATENATEX(OFFSET(1,ORDERBY('Table (2)'[FINISH DATE],ASC)),[FINISH DATE],"|"),1)
VAR Is_Blank = Data=BLANK()
RETURN IF(Is_Blank,"NULL", ABS(DATEDIFF(VALUE(Data),MAX('Table (2)'[FINISH DATE]),DAY)))

 

 

If your actual data's [FINSIH DATE] is not "sorted naturally", I think you should include a [ROW NUMBER] column in your dataset, created either by SQL selection or other means you used, and then use that [ROW NUMBER] column in your DAX, like the following (you also need to include that [ROW NUMBER] column in your table vis, sorted ascendingly. you can adjust the width of that column to 0 for formatting):

 

Days Between = 
VAR Data = 
PATHITEM(CONCATENATEX(OFFSET(1,ORDERBY('Table (2)'[ROW NUMBER],ASC)),[FINISH DATE],"|"),1)
VAR Is_Blank = Data=BLANK()
RETURN IF(Is_Blank,"NULL", [use your own preferred version of calculation here, see above])

 

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

hi @bhattg , does this answer your question? If so, kindly accept it as a solution so others facing similar issuescan look for this thread.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
johnyip
Super User
Super User

Hi @bhattg , can I know how to determine the start date / time? Without that info, it is hard to assist.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

I was trying the following expression but this calculates days between Min and Max date (2 parents), does not work with more than 2 Parents. Saw this on another post.

 

Measure = 
VAR MAX_Date =
    CALCULATE (
        MAX ( Table1[Finishdate] ),
        ALLEXCEPT(Table1,Table1[Parent])
    )
VAR MIN_Date =
    CALCULATE (
        MIN( Table1[FinishDate] ),
        ALLEXCEPT(Table1,Table1[Parent])
    )
RETURN
    DATEDIFF ( MIN_Date,MAX_Date, DAY )

 

Yes, by this DAX it obviously calculates the difference between the largest and smallest date within your data, which is not you wish to calculate.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Days Diff will start for each Parent with Type X, therefore, for the first Parent (112233) and its four Children, start date will be determined by Type "X".

 

I hope i answered your question.

  

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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