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

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

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

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

      Hi MarkCBB

       

      I think there shouldn't be any performance difference.