How to sort & return latest cell value base on latest Date & time with multiple timeslots in a day?

[Anonymous]

hi'

Had lately required to return KWH(AUX) cell value based on combine date and time column in PowerBI. 

However, the ascending sort order for the time is complicated:

Day 1.....Day 30,

2300

0700

1500

As such, may anyone advised on the below measure/ DAX:

_MSP_VALUE =

Var _Day_Time = LASTDATE(SHIFT_CONSUMPTION_EXCEL[Date_Time])

Var _MSP_Aux = CALCULATE(VALUES(SHIFT_CONSUMPTION_EXCEL[MSP AUX TOTAL (ACTUAL)]),SHIFT_CONSUMPTION_EXCEL[Date_Time]=_Day_Time)

RETURN

IF(ISBLANK(_MSP_Aux),0,_MSP_Aux)

Cheers,

Jane

[v-henryk-mstf]

Hi @Anonymous ,

The measure you provided seems to be trying to return the value of the column MSP AUX TOTAL (ACTUAL) for the most recent date and time in the column Date_Time.

First, the measure defines a variable _Day_Time which gets the most recent date and time value in the Date_Time column using the LASTDATE function. Then, it defines another variable _MSP_Aux which gets the value of MSP AUX TOTAL (ACTUAL) filtered to only the rows where Date_Time is equal to _Day_Time.

Finally, the measure returns _MSP_Aux if it is not blank, or 0 if it is blank.

It's important to note that the LASTDATE function will only return the most recent date value, not the most recent date and time value. If you want to get the most recent date and time value, you may need to use a different function or a combination of functions.

For example, you could use the MAX function to get the maximum value in the Date_Time column, like this:

Var _Day_Time = MAX(SHIFT_CONSUMPTION_EXCEL[Date_Time])

Alternatively, you could use the CALCULATE function with a filter argument to get the maximum Date_Time value for the current context, like this:

Var _Day_Time = CALCULATE(MAX(SHIFT_CONSUMPTION_EXCEL[Date_Time]), ALL(SHIFT_CONSUMPTION_EXCEL[Date_Time]))

I hope this helps! Let me know if you have any other questions.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Henry,

thank you for the kind advise.

We had actually applied max to both date and time columns.

the result returned was simply not to the actually value.

e.g.:

date 28/12/22

2300  1000

0700   8000

1500    6500

Date : 29/12/22

2300   -

0700 -

1500 -

so we had assigned index to both the time and date columns.

so in our experiment , we call for the latest non blank cell in stating ' -3,' in our measure, which ought to return the latest value of 1000.

the returned value appeared blank instead of 1000.

henceforth, we are seeking to understand what could have been the mismatch in our written measure.

cheers,

jane