2nd last date per item

[MarkCBB]

Hi there,

I need to create a measure that filters another measure to the 2nd last date.

for example, For BP Charles I would like the measure to return the 41.2% score as it is the 2nd last score for that store.

 The end result would be a table with Store in the Rows, and the following columns 1st Last Score, 2nd Last Score, 3rd Last score 

I have a fact table as well as a calendar table:

Here is the measure that I am using to get the average over all of the dates:

TOTAL AVERAGE SCORE = 
VAR BASE = (IF([CORP %]=BLANK(),BLANK(),1) + IF([FRANCHISE %]=BLANK(),BLANK(),1) + IF([FORECOURTS %]=BLANK(),BLANK(),1))
RETURN
([CORP %]+[FRANCHISE %]+ [FORECOURTS %])/BASE

Here is one of the measures used in the above, the others are the same:

CORP % = 

Var MyFormChannel = "STORE AUDIT FORM - PNP CORP HYPER AND CHECKERS"
RETURN
DIVIDE(CALCULATE(COUNTROWS(DATA),DATA[sAnswer]="YES",DATA[FORM TYPE]=MyFormChannel),CALCULATE(COUNTROWS(DATA),DATA[QUESTION]<>"NA",DATA[FORM TYPE]=MyFormChannel))

Regards,

Mark

[Zubair_Muhammad · Accepted solution]

Hi Mark @MarkCBB

Please try this MEASURE

Measure =
VAR SecondLastDate =
    MINX (
        TOPN (
            2,
            CALCULATETABLE (
                VALUES ( TableName[DATE] ),
                ALLEXCEPT ( TableName, TableName[STORE] )
            ),
            TableName[DATE], DESC
        ),
        TableName[DATE]
    )
RETURN
    CALCULATE (
        [TotalAverageScore],
        FILTER (
            ALLEXCEPT ( TableName, TableName[STORE] ),
            TableName[DATE] = SecondLastDate
        )
    )

[MarkCBB]

Hello @Zubair_Muhammad,

Thank you,  I was able to get this to work. I do however have a question, If I have a look at BP Clubview there is only 1 occurrence. Thus there should be a result, How would I edit this to show the result only if there is a 2nd occurrence. 

[Zubair_Muhammad]

@MarkCBB

Would you like to show BLANK in that case?

[MarkCBB]

Hello @Zubair_Muhammad,

I was able to do this after messing around a bit. This is the approach I took, I would love to hear your take on it.

First I created a new measure to get a Distinct Count:

Dist Count = COUNTROWS(SUMMARIZE(DATA,DATA[STORE],DATA[DATE]))

Then I edited your measure by adding a logical condition: (The IF)

2nd Last Visit = 
Var Occurrence = 2
VAR SecondLastDate =
    MINX (
        TOPN (
            Occurrence,
            CALCULATETABLE (
                VALUES ( 'DATA'[DATE] ),
                ALLEXCEPT ( DATA, DATA[STORE] )
            ),
             'DATA'[DATE], DESC
        ),
         'DATA'[DATE]
    )

RETURN
IF( [Dist Count] >=Occurrence ,
    CALCULATE (
        [TOTAL AVERAGE SCORE],
        FILTER (
            ALLEXCEPT ( DATA, DATA[STORE] ),
             DATA[DATE] = SecondLastDate
        )
    ),BLANK())

[Zubair_Muhammad]

@MarkCBB

Great work Mark.

I wrote a similar revised MEASURE

Measure =
VAR SecondLastDate =
    MINX (
        TOPN (
            2,
            CALCULATETABLE (
                VALUES ( TableName[DATE] ),
                ALLEXCEPT ( TableName, TableName[STORE] )
            ),
            TableName[DATE], DESC
        ),
        TableName[DATE]
    )
VAR countdates =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( TableName[DATE] ),
            ALLEXCEPT ( TableName, TableName[STORE] )
        )
    )
RETURN
    IF (
        [countdates] > 1,
        CALCULATE (
            [TotalAverageScore],
            FILTER (
                ALLEXCEPT ( TableName, TableName[STORE] ),
                TableName[DATE] = SecondLastDate
            )
        )
    )

[Zubair_Muhammad]

@MarkCBB

Difference is that you had used a MEASURE while I used a VARIABLE

VAR countdates =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( TableName[DATE] ),
            ALLEXCEPT ( TableName, TableName[STORE] )
        )
    )

[MarkCBB]

@Zubair_Muhammad, Thank you so much for your help, I have learnt something new. 

Quick question, would there be any performance difference between the 2 approaches?

[Zubair_Muhammad]

Hi @MarkCBB

I think there shouldn't be any performance difference.