Is there a way to implement an event-specific DATEDIFF?

[xuri]

Hello,

I want to calculate the time difference of same type occurrences. What I already did was to get my data in the following format by duplicating the table (and adding index columns, one beginning with 0 and the other with 1) and merging them by means of a outer join. Then I added a calculated table: Date difference in days = DATEDIFF('Duplicate1'[Date of event]; 'Duplicate 1'[Next event]; DAY). The result can be seen in "What I achieved".

The table I created is only valid if same type events are filtered, otherwise it will show the next date of the next index, which is not visible due to active filters but whose date will be inserted as next event. This is shown in "What I assume to be possible". In order to get the correct behavior I want to display a warning.

WARNING = IF(HASONEVALUE('OriginalTable'[Event]); ""; "WARNING: The time differences are only valid for one value in the query editor. Please select one event via the query editor!")

However if any slicers or filters are active on report level the warning shall be displayed. It shall only vanish if only the dates of a specific event are calculated - which is true if this is done via the query editor. 

As an extension I'd like to do that for events with the same name but at different locations. This would look like this for the same problem:

Is it possible to realize what I am trying to achieve or is there a workaround or alternative solution for this? The optimal solution would be to get my data into the format "What I actually want", but I cannot think of a way to do so.

Thanks in advance! Any hints and solutions will be deeply appreciated!

xuri

[Eric_Zhang · Accepted solution]

@xuri

I created a demo for your case, please check the attached pbix file. The key measures are like

new event date = 
CALCULATE (
    MIN ( Table1[Date of event] ),
    FILTER (
        CALCULATETABLE(Table1,ALLEXCEPT(Table1,Table1[Event])),
        Table1[Event Order] > MAX ( Table1[Event Order] )
    )
)


new event date_ = 
CALCULATE (
    MIN ( Table2[Date of event] ),
    FILTER (
        CALCULATETABLE ( Table2, ALLEXCEPT ( Table2, Table2[Event],Table2[Location] ) ),
        Table2[Event Order] > MAX ( Table2[Event Order] )
    )
)

 

[xuri]

@Eric_Zhang

Thank you so much! This is exactly the approach I have been looking for.

In addition I have two remarks:

1. I noticed that for a large dataset the measure new event date cannot be displayed next to the Date of event since I got the error message:

"There's not enough memory to complete this operation. Please try again later when there may be more memory available."
However, the time difference is the important information and the next date can be seen on the table below the referring current date, so the extra displaying of new event date is not required.

2. I realized that the calculation of the time difference depends on the goal and wanted to add another aspect. In some cases, the time difference always refer to the first date - which has been realized in the demo file by means of the measure diff days:

diff days = IF(ISBLANK([new event date_]), "", FORMAT( INT([new event date_]-MIN(Table2[Date of event])), "0"))

If the time difference to the previous date is required, the measure diff days will be without the minimum function:

diff days = IF(ISBLANK([new event date_]), "", FORMAT( INT([new event date_]-Table2[Date of event]), "0"))