Last Occurrence a common problem

Power BI
1

I’m looking for a solution video in the Learning Center for this common problem … if it does not exist then the incredible Enterprise DNA should make one for all of us to help us.

Last occurrence.pbix (245.1 KB)

occurence
occurence1308×668 133 KB

Last Occurrence

I have

Find the last occurance for each No Question

I uploaded a Power BI file here to make things easier.

So, I need for each item number to find which one is the current valide item.
The problem here is the Inactive Date most of the time is 31-12-2100 so I need to find the Max for that item in both Active Date and Inactive Date
for each item.

What would be the best solution for this ?
An extra column with a flag that marks which one of the items is the correct one ?

Thanks in advance

Eric (Montreal, Canada)

2

@ericet,

Consider it done. I’ve got a super simple and powerful way to handle this type of problem, and need to put together a video anyway for early next week.

I’ll crank that out and get it to the video editor today. It fits in beautifully with a recent series I’ve been doing on the PQ Group By function.

Thanks a ton for the suggestion.

– Brian

3

@ericet,

In thinking more about this question, I believe we’ve already addressed it quite thoroughly in the following videos from both a DAX and Power Query perspective:

See what you think.

  • Brian
4

Yes, almost the solution. I can now figure out which record I need but now I need to filter it out the other rows.

I only want the last valid occurance to be displayed in the table. I would need a flag be there when Active Date = Max Date Active and filter out everything that does not have a flag from the table.

But how do I do that ? When I want to do a measure to make my flag, it does not see occurance[Date Active] so I cannot do a if or datebetween etc to generate a flag that I can after filter out using the Filters pane.

Close but not there yet.

Here is the updated Power BI file.
Last occurrence.pbix (246.4 KB)

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
6

Hi Brian,

Awesome ! Yes it works. Thank You Very Much.

I just used your measure, no need in this example to add a date table. In my real Power BI I’m building everything is there including then date table. I just made a quick Power BI file with my problem to be able to share with everyone and have everyone benefit from this.

Here is the final file and an image of the solution.

Thanks again

Eric (Montreal, Canada)

IsMax Active =
IF(
SELECTEDVALUE( ‘Occurance’[Date Active] ) = [Max Date Active],
1,
0
)

Occurence Solution
Occurence Solution1310×724 225 KB

Last occurrence.pbix (246.9 KB)

7

@ericet,

Great - glad that got you what you needed. Nice touch with the literal green icon flags. :grinning:

  • Brian