Showing Previous Month and Week Data

Hi tainguyen ,

 

You need a MonthNo column in ScoreCard table.

MonthNo =
SWITCH (
    TRUE (),
    ScoreCard[Month] = "January", 1,
    ScoreCard[Month] = "Febuary", 2,
    ScoreCard[Month] = "March", 3,
    ScoreCard[Month] = "April", 4,
    ScoreCard[Month] = "May", 5,
    ScoreCard[Month] = "June", 6,
    ScoreCard[Month] = "July", 7,
    ScoreCard[Month] = "August", 8,
    ScoreCard[Month] = "September", 9,
    ScoreCard[Month] = "October", 10,
    ScoreCard[Month] = "November", 11,
    ScoreCard[Month] = "December", 12
)

Also, create a calculated table which lists unique Month names and Weeks. Make sure this table is unrelated to ScoreCard table.

Slicer table =
CROSSJOIN ( VALUES ( ScoreCard[Week] ), VALUES ( ScoreCard[Month] ) )

Then, add below measures together with 'ScoreCard'[Trainee] into visual.

Curr_Meetings =
CALCULATE (
    SUM ( ScoreCard[Meetings] ),
    FILTER (
        ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),
        ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )
            && ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )
    )
)

prev_Meetings =
VAR curr_Monthno =
    CALCULATE (
        MAX ( ScoreCard[MonthNo] ),
        FILTER (
            ALLSELECTED ( ScoreCard ),
            ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )
        )
    )
RETURN
    CALCULATE (
        SUM ( ScoreCard[Meetings] ),
        FILTER (
            ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),
            ScoreCard[MonthNo] = curr_Monthno - 1
                && ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )
        )
    )

 

Best regards,

Yuliana Gu