Dynamic DATEDIFF problem

[Yamabushi]

Hello,

I am trying to calculate the date difference between various rows in minutes as follows:

• I need to know the date difference between transactions for each person individually.

• If two of the transactions from the same person happened simultaneously (down to the second), I want the difference between them to be 0.

Below is the sample data and how far along I managed to get (I filtered out a single person for convenience):

Difference from previous = DATEDIFF(CALCULATE(MAX('Sample'[Date and time of transaction]);FILTER('Sample';EARLIER('Sample'[Name])='Sample'[Name]&&EARLIER('Sample'[Date and time of transaction])>'Sample'[Date and time of transaction]));'Sample'[Date and time of transaction];MINUTE)

Now as you can see, the problem is that two transactions happened at the same moment, but DAX ignores that and instead just uses the previous transaction that was done at a different time. How can I solve this? I've tried || and >= but then I just get zeros everywhere.

After this has been done, I would like to be able to filter the data based on transaction type and get just the differences between the currently shown data. How can this be done?

Thank you very much!

[Greg_Deckler]

I think you are going to need to use an Index to solve this. So you will need that and will want to check how many rows you have of the same sample and date/time. If you have multiple, then check your Index. If your current row is the MIN index, then >, otherwise, >=. If you do not have multiple, then >.

[Yamabushi]

Hi @Greg_Deckler,

Thank you for you reply! Would you care to elaborate a bit more in depth? I'm not sure I quite understand, I'm not really an expert in this stuff.

Thank you!

[v-frfei-msft]

Hi @Yamabushi,

Firstly, I insert an index column in Power query. after that, I create a calculated column as below. If I misunderstood your requirement, kindly share your excepted result to me.

Column 2 =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table1'[Date and time of transaction] ),
        FILTER (
            Table1,
            'Table1'[Transaction type] = EARLIER ( Table1[Transaction type] )
                && Table1[Index] < EARLIER ( Table1[Index] )
        )
    ),
    'Table1'[Date and time of transaction],
    MINUTE
)

Regards,

Frank

[Yamabushi]

Hi @v-frfei-msft,

If I have 2 transactions at the same time, I would like the first transaction to show the difference from the previous transaction (as it was at a different time). The next transaction (which is at the same time as the previous transaction), should show 0.

So the solution is not quite what I'm looking for yet. I want the second row to show 1535 and the third row to show 0.

Hope this is not too complicated.

Best regards

[v-frfei-msft · Accepted solution]

Hi @Yamabushi ,

To use this formula to create a calcualted column.

Column 2 = 
VAR ind = Table1[Index] - 1
VAR pre =
    CALCULATE (
        MAX ( 'Table1'[Date and time of transaction] ),
        FILTER ( Table1, Table1[Index] = ind )
    )
RETURN
    DATEDIFF ( pre, 'Table1'[Date and time of transaction], MINUTE )

Regards,

Frank

[Yamabushi]

Hi @v-frfei-msft ,

Great, this seems to work ok!

Thank you!