How To Select The Recent Value in a Day ?

[Anonymous]

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 ?

[JosefPrakljacic]

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]

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

Divo

[JosefPrakljacic]

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]

Hi @JosefPrakljacic ,

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

[JosefPrakljacic]

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!

BR,
Josef

[Anonymous]

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.