Difference In Time of Events In Direct Query Mode

[moizsherwani]

Hello everyone,

So thanks to @Eric_Zhang I was able to rank my projects according to time (link below)

http://community.powerbi.com/oxcrx34285/board/message?board.id=power-bi-designer&message.id=115834#M...

What I need now is a measure that will calculate the difference in the time of one event and the next in days for the same project. Example below (for the last event the difference will be the difference between now and that event). I have added a phony column called NextEventTime to explain my point.

Project - EVENTID  - TIME            - NEXTEVENTTIME    - TIME DIFF  - RANK  

A          - 1234        - 1/1/2017     -  1/7/2017                -     6            - 1

A          - 2345        - 1/7/2017     -  1/14/2017              -     7            - 2 

A          - 3456        - 1/14/2017   -  9/28/2017              -     257         - 3

B          - 7890         - 6/1/2017    -  6/7/2017                 -     6            - 1

B          - 8901         - 6/7/2017    -  6/14/2017               -     7            - 2

B          - 9012          - 6/14/2017  - 9/28/2017               -    106         - 3

C          - 6543          - 9/1/2017    - 9/28/2017               -    27           - 1

D          - 9876          - 8/1/2017   -1

D          - 7865          - 8/7/2017   -2

[v-jiascu-msft · Accepted solution]

Hi @moizsherwani,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[moizsherwani]

@v-jiascu-msft Thanks so much for your response. This doesn't work in Direct Query Mode though. Did you have that in mind when providing the proposed solution?

[moizsherwani]

@v-jiascu-msft Thanks so much for your response. This doesn't work in Direct Query Mode though. Did you have that in mind when providing the proposed solution?

[v-jiascu-msft]

Hi @moizsherwani,

I think a calculated column would be a good solution. But you still can try it as a measure.

1. Calculated column.

1) One step.

TimeDiffOnestep =
VAR currentProject = 'Table1'[Project]
VAR currentTime = 'Table1'[TIME]
VAR nextEventTime =
    CALCULATE (
        MIN ( 'Table1'[TIME] ),
        FILTER (
            'Table1',
            'Table1'[Project] = currentProject
                && 'Table1'[TIME] > currentTime
        )
    )
RETURN
    IF (
        ISBLANK ( nextEventTime ),
        TODAY () - currentTime,
        nextEventTime - currentTime
    )

2) Two steps.

NextEventTime =
VAR currentProject = 'Table1'[Project]
VAR currentTime = 'Table1'[TIME]
VAR nextEventTime =
    CALCULATE (
        MIN ( 'Table1'[TIME] ),
        FILTER (
            'Table1',
            'Table1'[Project] = currentProject
                && 'Table1'[TIME] > currentTime
        )
    )
RETURN
    IF ( ISBLANK ( nextEventTime ), TODAY (), nextEventTime )

TimeDiffTwoSteps =
[NextEventTime] - [TIME]

2. As a measure.

MeasureSolution =
VAR currentProject =
    MIN ( 'Table1'[Project] )
VAR currentTime =
    MIN ( 'Table1'[TIME] )
VAR nextEventTime =
    CALCULATE (
        MIN ( 'Table1'[TIME] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Project] = currentProject
                && 'Table1'[TIME] > currentTime
        )
    )
RETURN
    IF (
        ISBLANK ( nextEventTime ),
        DATEDIFF ( currentTime, TODAY (), DAY ),
        DATEDIFF ( currentTime, nextEventTime, DAY )
    )

Best Regards!

Dale