Forum Discussion

MarkCBB's avatar
MarkCBB
Icon for Helper V rankHelper V
8 years ago
Solved

2nd last date per item

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...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
    MarkCBB
    Icon for Helper V rankHelper V
    8 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      MarkCBB

       

      Would you like to show BLANK in that case?

      • MarkCBB's avatar
        MarkCBB
        Icon for Helper V rankHelper V
        8 years ago

        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())