Forum Discussion

newgirl's avatar
newgirl
Post Patron
7 years ago
Solved

Recent Value

Hi!   I am relatively new in using Power BI. In the current file I'm working on, I am facing some challenges.   Here are my sample tables (refer to first 2 screen shots)   Main Table - combined...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi newgirl 

    You may try below measure.Attached sample file for your reference.

    Measure = 
VAR recent_date =
    CALCULATE (
        MAX ( Category[MonthYear] ),
        FILTER (
            ALL ( Category ),
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] <= MAX ( 'Date'[MonthYear] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Category[Category] ),
        FILTER (
            Category,
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] = recent_date
        )
    )

    Regards,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi newgirl 

You may try below measure.Attached sample file for your reference.

Measure = 
VAR recent_date =
    CALCULATE (
        MAX ( Category[MonthYear] ),
        FILTER (
            ALL ( Category ),
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] <= MAX ( 'Date'[MonthYear] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Category[Category] ),
        FILTER (
            Category,
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] = recent_date
        )
    )

Regards,

  • newgirl's avatar
    newgirl
    Post Patron
    7 years ago

    Thank you so much v-cherch-msft !!! This worked perfectly. You are heaven-sent!