none
PowerPivot get last update

    Question

  • My table looks like this:

    FLIGHT | UPDATE | ETA | ARRIVAL

    A | 2/6/14 | 6/18/14 | 6/16/14

    A | 6/6/14 | 6/19/14 | 6/16/14

    B | 1/7/14 | 6/2/14 | 6/26/14

    B | 6/16/14 | 7/2/14 | 6/26/14

    B | 6/23/14 | 6/26/14 | 6/26/14

    I need to get the latest ETA update 7 days prior arrival. For instance: Flight A arrived on 6/16. 7 days before this was 6/9. The latest update before this date was 6/6. On that date, the provided ETA was 6/19. So my measure would return:

    A -> 6/19/2014

    B -> 7/2/2014

    How would you write this in PowerPivot?

    Thanks

    Thursday, July 17, 2014 10:02 PM

Answers

All replies

  • I haven't had a chance to test this, but it should get you started:

    measure :=
    CALCULATE (
        VALUES ( '--tablename--'[ETA] ),
        FILTER (
            ALL ( '--tablename--'[UPDATE] ),
            '--tablename--'[UPDATE]
                = CALCULATE (
                    MAX ( '--tablename--'[UPDATE] ),
                    FILTER (
                        ALL ( '--tablename--'[UPDATE] ),
                        '--tablename--'[UPDATE]
                            < VALUES ( '--tablename--'[ARRIVAL] ) - 7
                    )
                )
        )
    )

    Friday, July 18, 2014 6:23 PM
  • Thanks Greg.

    I'm getting the following error:

    ERROR - CALCULATION ABORTED: Calculation error in measure 'Tbl'[Measure 1]: A table of multiple values was supplied where a single value was expected.

    I believe it is due to VALUES (Tbl[ARRIVAL] ).

    Any idea?

    Friday, July 18, 2014 8:57 PM
  • Yes, you're right there. Replace that VALUES() with a MAX() or MIN(). Like I said, I didn't get a chance to test this out.
    Monday, July 21, 2014 2:48 PM
  • Thanks Greg, this is awesome.
    Would you know how to modify the formula so that I get the latest ETA update 4 WORKING days prior arrival -- instead of 7 days?
    Your help is greatly appreciated

    Tuesday, July 22, 2014 6:25 PM
  • You'll need to add a working day flag to your data, either in a date table related to [UPDATE] or in the fact table itself. Then modify the last bit of the formula (before all the closing parentheses) to this:

    ALL ( '--tablename--'[UPDATE] ),
                        '--tablename--'[UPDATE]
                            < VALUES ( '--tablename--'[ARRIVAL] ) - 7 && '--tablename--'[WorkingDayFlag] = 1

    Tuesday, July 22, 2014 6:56 PM