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,

 

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

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