cancel
Showing results 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.

Anonymous
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:

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 ?

6 REPLIES 6
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? -> https://www.sqlbi.com/daxpuzzle/last-date/

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

Have a nice day!

BR,
Josef

Anonymous
Not applicable

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

Divo

Solution Sage

Hey @Anonymous ,

here is another try (here is the example file)

```Measure =
LASTNONBLANK (
'Measurement'[MeasurementValue],
SELECTCOLUMNS (
FILTER (
Measurement,
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!

BR,
Josef
Anonymous
Not applicable

Below is my model:

Below is a sample data

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

Solution Sage

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.

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

@Anonymous Have a nice day!

BR,
Josef

Anonymous
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]
return
CALCULATE(SUM(measurement[measurementvalue]), measurement[measurementid] = maxid)```

Not sure why it wants two separate measures.

Announcements

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors