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 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 ?

 

Need Help

6 Replies

  • 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