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
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
Solved! Go to 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 )
)
Best Regards,
Community Support Team _ Eason
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]))
@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])
@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
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 )
)
Best Regards,
Community Support Team _ Eason
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |