How to select the last value of the day with DAX in Power BI

0
votes

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 in Measurement Fact

Below is a sample data:

enter image description here

NB: In edit query, I have changed the type of measuredate to Date only.

I have tried the measure below but it doesn't work the way I want. It will sum all the values of the day but what I want is the last value of the day:

day_fuel_consumption =
CALCULATE (
    SUM ( measurement[measurementvalue] ),
    FILTER (
        measurement,
        measurement[metername] = "C-FUEL"
     && measurement[measuredate] = MAX ( measurement[measuredate] )
    )
)

My Goal is to get 29242, i.e the last value of the day. Remember that measuredate is a Date field and not Datetime (I changed to Date field so that my Year and Month filter can work correctly). I have changed the type in edit query.

3
powerbi
I recently answered a very similar question. It should be fairly straightforward to convert that pattern to use a single DATETIME field, rather than split dates and times. – greggyb

3 Answers

2
votes

Changing your measure to use a variable could be the solution:

DFC = 
var maxDate = MAX(measurement[measuredate])

return
CALCULATE(
    SUM(measurement[measurementvalue]), 
    measurement[measuredate] = maxDate
)

However, you should keep the datetime format for measureDate. If you don't want to see the time stamp just change the format I power bi. Otherwise power bi will see two values with max date and sum them, instead of taking the last one.

0
votes

Well, if you want to avoid creating a measure, you could drag the fields you are filtering over to the visual filters pane. Click your visual, and scroll a tiny bit and you will see the section I am referring to. From there, just drag the field you are trying to filter In this case, your value. Then select "Top N". It will allow you to select a top (number) or bottom (number) based on another field. Strange enough, it does allow you to do top value by top value. It doesn't make sense when you say it out loud, but it works all the same. This will show you the top values for whatever value field you are trying to use. As an added bonus, you can show how little or how many you want, on the fly.

As far as DAX goes, I'm afraid I am a little DAX illiterate compared to some other folks that may be able to help you.

0
votes

I had to create two separate measures as shown below for this to work as I wanted:

max_measurement_id_cf = CALCULATE(MAX(measurement[measurementid]), FILTER(measurement, measurement[metername] = "C-FUEL"))

DFC = 
var max_id_cf = [max_measurement_id_cf]
return 
CALCULATE(SUM(measurement[measurementvalue]), measurement[measurementid] = max_id_cf)