Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Find The Last Rate at the end of each day

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

1 ACCEPTED SOLUTION

Hi, @Anonymous 

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

1.png

Best Regards,
Community Support Team _ Eason

View solution in original post

5 REPLIES 5
Whitewater100
Solution Sage
Solution Sage

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
Super User
Super User

@Anonymous , 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])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@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

Anonymous
Not applicable

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."

Hi, @Anonymous 

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

1.png

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.