Help to calculate the subtraction of two dates from the same column and with conditions

[Anonymous]

Hello people,

I am new to Power BI and I have been looking for several solutions for what I am looking for and I have not been able to find the solution.

I have a database, that, in summary, has several columns as seen in the image:

I want to generate a column that counts the minutes between two dates, as long as they share the same place "PLACE" and "PLATAFORMA". And the dates that want to subtract are the date after the current one. So not all rows will have a value.

The idea is to generate this new column in DAX.

[FreemanZ]

hi @Anonymous 

you may add a calculated column like this:

DIFF = 
VAR _datetimenext =
MINX(
    FILTER(
        TableName,
        TableName[PLACE] = EARLIER(TableName[PLACE])
            &&TableName[PLATAFORMA] = EARLIER(TableName[PLATAFORMA])
            &&TableName[DATE AND TIME]>EARLIER(TableName[DATE AND TIME])
    ),
    TableName[DATE AND TIME]
)
VAR _diff = DATEDIFF([DATE AND TIME], _datetimenext, MINUTE)
RETURN
IF(_datetimenext<>BLANK(), _diff)

[Anonymous]

It's no working for me.

I'm using other names for columns' titles. 

My actual columns are these:

I only need the results for "PUNTA ALVEAR", so this value could be constant.

The code I wrote, changing the one you told me is:

And appears that mistake

Can you help me?

[TomasAndersson]

The DATEDIFF at the end has "minutes" as its third argument. It should be MINUTE.

[Anonymous]

I changed that part and it's going to appear the same mistake 😭

EDIT: I found de mistake, line 10, there was to brackets

[TomasAndersson]

Ah, saw that now as well. Hope it works now!

[Anonymous]

Yes, it's working perfectly! Thank you so much!!!!

[TomasAndersson · Accepted solution]

Hi!

You can use this calculated column:

Minutes = 
VAR __PLACE = 'Table'[PLACE]  //store variables for this row's place, platform and time
VAR __PLATAFORMA = 'Table'[PLATAFORMA]
VAR __DATEANDTIME = 'Table'[DATE AND TIME]
VAR __NextDate =
CALCULATE(
    min('Table'[DATE AND TIME]),  //find the earliest time
    FILTER(ALL('Table'),  //of rows with same place and platform and a time after this row's time
        'Table'[PLACE] = __PLACE &&
        'Table'[PLATAFORMA] = __PLATAFORMA &&
        DATEDIFF( __DATEANDTIME, 'Table'[DATE AND TIME], MINUTE) > 0
    )
)
return
if(  //if there is a next date, return the time difference in minutes, else do nothing
    NOT ISBLANK(__NextDate),
    DATEDIFF( __DATEANDTIME, __NextDate, MINUTE)
)

Good luck!