Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 ...
Need Help
JosefPrakljacic's avatar
JosefPrakljacic
Solution Sage
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    JosefPrakljacic
    Solution Sage
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.