Find The Last Rate at the end of each day

[eliasayy]

Hello everyone, I am fairly new to powerbi and the dat analysis world. I was asked to create a graph that shows  the final rate by the end of each day. The table has a date column and rate column and a date/time column, every 5 minutes i have a different rate. My objective is to find the latest rate at the end of the day. Example in 4/20/2022, i have multiple rates 0.003 at 1 pm 0.005 at 5 pm and 0.008 at 11 pm. i want only the last value which is 0.008 to show on the graph and so on for the other days. please help

[Whitewater100]

Hi:

If you don't have a separate table, it would be a great idea to have one. This Date Table would connect to your Fact Table on a date filed in a one to many relationship. (Measure at end)

Here is a Date Table you can use by choosing new Table and using this DAX>

DATES =

  GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

  )

)

Last Value = LASTNONBLANKVALUE(Dates[Date], SUM(FACTTABLENAME[Rate Column]))

[amitchandak]

@eliasayy , Try measure like

Lastnonblankvalues(table[Datetime]), max(Table[Rate])

or

calculate(Lastnonblankvalues(table[Datetime]), max(Table[Rate]) , allexcept(Table, Table[Date]) )

if needed create a date column without timestamp

Date = datevalues([datetime])

[eliasayy]

@amitchandak , i find the solution just changed the location, my problem wasnt solved mostly. i have mutiple dates as in, april 18, april 19, april 20, april 21... and i need to find the last rate recorded of each single day, the formula gave me only the final rate at the end of the final day and it was all the same for the other dates. in other words i got the same rate for all days

[eliasayy]

Hello @amitchandak ,

i tried it and unfortunatley i kept egttin an error (Lastnonblankvalues(table[Datetime]), in this particular place where the error is in ")" that says expected additional parameters and "Too few arguments were passed to the LASTNONBLANKVALUE function. The minimum argument count for the function is 2."

[v-easonf-msft · Accepted solution]

Hi, @eliasayy 

Please try the following formula:

Last Rate of each day =
VAR lastdatetime =
    CALCULATE ( MAX ( 'Table'[Datetime] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Rate] ),
        FILTER ( 'Table', 'Table'[Datetime] = lastdatetime )
    )

Best Regards,
Community Support Team _ Eason