Finding the previos occurrence within same table based on one criteria

[CHJustesen]

Hi guys,

I have a list of time registers from users registering that they have been spending time on a customer case, as well as if they have held a break. I would like to find the user who previously registered time on the same case. My data and my goal looks like this:

Entry No    User     Case        OUTCOME

100            V1        492524    

101            V3        502392    

102            V3        492524    V1

103            V4        492524    V3

104            V1        BREAK

105            V3        492524    V4

I have tried with the calculate and the lookupvalue function without any luck.

Hope that someone can be of help! 🙂

Best regards

Claus

[v-chuncz-msft · Accepted solution]

@CHJustesen,

You may add a calculated column as shown below.

Column =
MAXX (
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Case] = EARLIER ( Table1[Case] )
                && Table1[Entry No] < EARLIER ( Table1[Entry No] )
        ),
        Table1[Entry No], DESC
    ),
    Table1[User]
)

[jimbob2285]

Hi

I've used this DAX, which is giving me a value from the earlier record, but I need a value from the later record, although I can't figure out how to adapt the DAX, there's no Later alternative to Earlier

Can you help please

Cheers

Jim

[Anonymous]

@v-chuncz-msft

I hope this is not too much to ask, as the post is solved, but...

Could you please briefly clarify my doubts? I have notes in between on what I think it is.

   - Why the use of MAXX and TOPN together?

         - I suppose it is just to get one value out of the calculation, right? The maximum (newest) Entry No and the only user of that TOP 1?

   - What is the EARLIER function purpose and why is it used here?

         - I'm really clueless about this one. Is it somehow related with date intelligence?

Thanks in advance.

Cheers!

[Anonymous]

Is there a way to find it out in the measure?
Without the calculated collumn?

In my example the table is based upon the table/matrix visualation, that is gathered based upon relevant collumns from other tables and measures such as:

MarginYTD = TOTALYTD([Margin Real+];'Calendar'[Date])

Table has a collumn with;
Company name     Margin-Year  -Q      -Month     -DAY

 

[CHJustesen]

Hi guys,

I have a table with a lot of time registers from all of our users registering time on customer-cases and when they are having breaks. If I have more than one time registration on the same case, I would like to find the user who previously registered time on the same case. I want to exclude the break time registrations as they are not needed. All the data is in the same table which could look like this:

Entry No   User      Case No     OUTCOME

100           V1         495723

101           V5         502312

102           V5         592932

103           V3         495723        V1

104           V4         495723        V3

105           V1         BREAK

I have tried with the calculate function with filters as well as the lookupvalue function but with no luck for me.

Hope that somebody can help me! 🙂