Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Kate_D
Frequent Visitor

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.

 

test.jpg

6 REPLIES 6
Kate_D
Frequent Visitor

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.

 Screen 2.jpgScreen2.jpg

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Kate_D
Frequent Visitor

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. 

 

 

Screen1.jpg

Kate_D
Frequent Visitor

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?

@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@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

 

DataInsights_0-1736984682250.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.