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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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 ?

 

6 REPLIES 6
JosefPrakljacic
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? -> 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

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

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

Hi @JosefPrakljacic ,

 

Below is my model:

 

Model.PNG

 

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!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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