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 December 2024. The Goal % is calculated as measure -

Goal % = DIVIDE(SUM(Sheet1[Revenue $]), SUM(Sheet1[Goal $])).
Is there a way to show the last available Goal % (November 2024 data) for Accounting and Consulting Departments in December 2024 instead on blank values? My struglgle is that Goal % is a calculated measure and not a column. Thank you.

 

6 Replies

  • 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

     

     

  • Kate_D's avatar
    Kate_D
    Frequent Visitor
    1 year ago

    Thank you for your assisstance. Is there a solution when there are no MonthEndDate values for some departments? Like 11/30/2024 and 12/30/2024 but we still want to show last avalable Goal % for 11/30/2024 and 12/30/2024 when using date filter?

    • DataInsights's avatar
      DataInsights
      Super User
      1 year ago

      Kate_D,

       

      Would you provide an example and the expected result? It's generally better to resolve data issues in Power Query. How would departments without MonthEndDate be selected via the date slicer?

  • Kate_D's avatar
    Kate_D
    Frequent Visitor
    1 year ago

    Let's say there was no Revenue $ or date recorded for Accounting department for 12/31/24, no Revenue $ or date for Construction department for 11/30/24, and no Revenue $ for 12/31/2024. 

     

     

  • Kate_D's avatar
    Kate_D
    Frequent Visitor
    1 year ago

    However, when filtering the data for 11/30/2024, we still want to show Sum of Revenue, Goal % and MonthEndDate for Construction which would be data for a previous period of 10/31/2024 where the solution your provided above would work. For instance, the last data for Construction department was posted for 10/31/2024. When running a report for all departments for 1/31/25, there is no data for construction, but we still would like to show the last avaialble data which was for 10/31/2024.

     

    • DataInsights's avatar
      DataInsights
      Super User
      1 year ago

      Kate_D,

       

      Thanks for the clarification. It seems the requirement is to infer MonthEndDate when a department is missing MonthEndDate. In the Accounting example, it's a simple matter of adding one month to the latest MonthEndDate. In the Construction example, however, the missing MonthEndDate is between two existing MonthEndDate (the logic would have to look for gaps to determine what MonthEndDate to assign). If there were a scenario where a department has two or more rows with missing MonthEndDate, additional logic would be required to determine which row gets assigned which MonthEndDate.

       

      The cleanest approach would be to correct the data as far upstream as possible (Power Query, SQL). This will simplify the DAX and optimize performance.