Forum Discussion

Kate_D's avatar
Kate_D
Frequent Visitor
1 year ago

Showing Last Measure Values

Hello. I am trying to figure out how to show the last available value returned by measure. For instance, as shown below, there are no Revenue $ values for Accounting and Consulting Departments in Dec...
DataInsights's avatar
DataInsights
Super User
1 year ago

Kate_D,

 

Try these measures:

 

Sum Revenue = SUM ( Sheet1[Revenue] )
Sum Goal = SUM ( Sheet1[Goal] )
Goal % = 
VAR vCurrentMonthEndDate =
    MAX ( Sheet1[MonthEndDate] )
VAR vLastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( Sheet1[MonthEndDate], DIVIDE ( [Sum Revenue], [Sum Goal] ) ),
        ALLSELECTED ( Sheet1 ),
        VALUES ( Sheet1[Department] ),
        Sheet1[MonthEndDate] < vCurrentMonthEndDate
    )
VAR vResult =
    IF (
        ISBLANK ( [Sum Revenue] ),
        CALCULATE (
            DIVIDE ( [Sum Revenue], [Sum Goal] ),
            Sheet1[MonthEndDate] = vLastNonBlankDate
        ),
        DIVIDE ( [Sum Revenue], [Sum Goal] )
    )
RETURN
    vResult