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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Not applicable

How To Select The Recent Value in a Day ?

Hi Guys,


I am new to power BI and stuck with an issue. I have my model as follows:


  • Date Dimension
  • Measurement Fact

The date column in date dimension is link to measuredate


Below is a sample data:


Recent Value.PNG

What I am attempting to do is to be able to get the latest measurementvalue for the day. e.g 29242 for 2019-09-03 and use in the sum below. For the week, I will be suming latest value of the day


My measure is :

day_fuel_consumption = CALCULATE(sum(measurement[measurementvalue]), measurement[metername]= "C-FUEL")

I am thinking about creating a calculated column to determine which value is latest before changing the type of my column to Date instead of Datetime in the query editor so that my date filter can work correctly. How can I go about this ?


Solution Sage
Solution Sage

Hey @Anonymous,


I would write it like this.


day_fuel_consumption = CALCULATE(sum(measurement[measurementvalue]), FILTER(ALL(Measurement), Measurement[Date] = MAX(Measurement[Date]))


You could also use LASTDATE in the second parameter of your calculation.

May I refer you to this excellent article from the SQLBI guys? ->


If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!


Not applicable

Hi @JosefPrakljacic ,


I have tried the measure but it doesn't work. It sums all the value of the day (2019-09-03). I want to get but the last value of the day i.e the 29242


Please advice



Hey @Anonymous ,


here is another try (here is the example file)

Measure =
        FILTER (
            Measurement[MeasureDate] = MAX ( Measurement[MeasureDate] )
        "MeasureDate", Measurement[MeasureDate]
 @Anonymous May I ask you to provide us with sample data. I'm not satisfied with the solution and with more input from you we would achieve a better result 🙂
If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

Not applicable

Hi @JosefPrakljacic ,


Below is my model:




Below is a sample data


Sample Data.PNG


Josef the first formula that you send sums all the values of a given day. In the case it does 26338 + 29242. But what I want is just the last value (29242). I think it sums all the values of the day because I changed measuredate in the query editor to a Date field and not Datetime (so that my Date and Year Filter in the Visuals can work correctly).

Hello @Anonymous 


that means that my last answer with LASTNONBLANKVALUE would work. 

I also created a second measure and used your measurementid as a flag for the last value.


Please look at this example file if it satisifies your needs.


If this post was helpful may I ask you to mark it as solution and give it some kudos?

@Anonymous Have a nice day!


Not applicable

Hi Josef,


I was unable to get your solution to work for me consistently. It worked for that small dataset I sent to you but when I connect to the whole data in the table it didn't work. I had to create two separate measures for it to work as shown below:


max_measurement_id = CALCULATE(MAX(measurement[measurementid]), FILTER(measurement, measurement[metername] = "CONSO-FUEL" ))

day_fuel_consumption = 
var maxid = [max_measurement_id]
CALCULATE(SUM(measurement[measurementvalue]), measurement[measurementid] = maxid)

Not sure why it wants two separate measures.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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