DateDiff between dates in different rows

DAX DAX Calculations
1

Attempting to calculate the DATEDIFF between to dates, both dates are in separate columns, however the dates of interest are in different rows.

The below images (yellow highlights) shows the 2 columns/rows i want to compute the DATEDIFF on (step 1 assigned on date and step 14 last action date)

The actual DATEDIFF between the 2 dates is approx. 238 hours.

How best would I
Date Diff - 1

my current DAX to calculate DATEDIFF on each is

Hours per Step = 
SUMX( 'Table1' ,
    DATEDIFF( 'Table1'[ Assigned On] , Table1[ Last Action Date] , MINUTE ) / 60 // divide by 60 to convert to hours
)

Any possible suggestions on how to write the DAX

Sample pbix attached.

Date Diff Rev-1.pbix (20.9 KB)

Regards
J

2

Just a question, don’t you need some kind of reference that will need to part of your calculation your order number.

All you are doing here is calculation each step time with no reference to a order number or job number.

Is your step 14 the last step in your process?
Keith

3

@Keith,

Thank you for your reply.

The model I have provided is very much abbreviated, I wanted just to create a very simple model to explain my problem. In this model the Step Number Text Column is providing the context for DATEDIFF calculation. The Index column is just allowing me to sort in the correct order.

I would assume that perhaps in my real model, we would end up with something like SELECTEDVALUE to drill down to the specific rows, or something of that nature.

Step 14 is not the last step in the process.

Did I answer your question?

Regards
J

4

did you check out the courses that might help you with a solution to your problem?

did you check youtube?

5

@jprlimey,

Give this a go:

Hours Difference =

VAR StartVal =
CALCULATE(
    MAX( Table1[ Assigned On] ),
    Table1[Step Number Text] = "Step 1"
)

VAR EndVal =
CALCULATE(
    MAX( Table1[ Last Action Date] ),
    Table1[Step Number Text] = "Step 14"
)

VAR Result =
DATEDIFF(
    StartVal,
    EndVal,
    MINUTE
)

RETURN
DIVIDE( Result, 60, BLANK() )

image

  • Brian
1 Like
6

@Keith

Yes I did check one of EnterpriseDNA you tube links https://www.youtube.com/watch?v=Q8iri3G1_x4

It looked promising at first glance, however in our case the Index number that was utilized in the above Youtube clip won’t work.
As over time we have added more process steps (continuous improvement), so when we first started the “Step #14” shows as index no 14 in my examples, Today with all the additional improvements the current Step #14 could well be Step #30, hence we would not have reliability with the Index number. The only thing we have that is unique is the Step Number Text column. Again for simplistic purposes the Step numbers shown are very detailed and unique in my real model.

Regards
J

7

@BrianJ,

Yes, it worked perfectly!,

Date Diff - 2

I also checked my real model, in both Power Bi and Excel (just to be sure)

Date Diff - 3

Thanks so much Brian.

I’m probably like you today, supposed to be off for MLK day, but find myself pushing my limits on Power BI!

Best Regards
J

8

@jprlimey,

Great – glad to hear that worked well for you.

– Brian

9

@BrianJ,

Curious question if I had to do an average measurements of time taken by month let’s say, how would it differ from current DAX measurement, below image would show Average by Fiscal Year & Month.

It would appear to do this I’d have to create a virtual table in my real data file?

Date Diff - 4

I added the following measure to get the average over FY and month

Avg = 
AVERAGEX(
    SUMMARIZE( 'NPC Database' ,
        'Date'[Fiscal Year] ,
        'Date'[Month] ) ,
        [Hours Difference]
)

I definitely see a problem, none of the values show be negative!

Date Diff -5
Regards
J

10

@jprlimey,

No, I think you could do it using AVERAGEX, branching off the measure we just created above.

My recommendation would be to start a separate thread, and provide some sample data and we can work through the specifics of that one.

  • Brian
11

@BrianJ

Sorry I haven’t prepared anything to post yet regarding the Average issue, hopefully I’ll be afforded more time tomorrow to post an updated pbix file etc.

Regards
J