Last Occurrence a common problem

Power BI
5

@ericet,

You’re on a good track here, but some preliminary prep is needed first.

  1. Add a Date table - @Melissa’s Extended Date Table is the best choice and there’s not even a close second.
  2. Mark the Date table as a date table - critical for getting rid of all the little temporary date tables that are created automatically by PBI until you have a marked date table, and also for ensuring that time intelligence functions operate properly
  3. Create relationships btw your date table and your fact table:

image
image576×509 16.9 KB

  1. Now we can create the flag measure for Active Date = Max Date Active:

IsMax Active =

 IF(
    SELECTEDVALUE( 'Occurance'[Date Active] ) = [Max Date Active],
    1,
    0
)
  1. Now drop this in the filter pane, set equal to 1, and you should be good to go:

image
image725×470 16.4 KB

I hope this is helpful. Full solution file attached.

1 Like