Forum Discussion
Difference Between Oldest and Latest Value
Good Day,
I have a very simple problem that I can not solve. I simply want to get the difference between the most recent and the oldest value in a table (as Delta). I want to use the most recent (as Today) and the oldest (as Oldest) and determine the % change: (Oldest - Today)/(Oldest).
Returning values as a scalar turns out to be a nightmare in PBI. Thus I created a workaround to use measures to flag the Lastest and Oldest values in my table. Use a measure to only sum the values that are flagged. Now I get a different problem. I can see the values in my table, but they don't show in my cards (Sum total shows).
Below is a screenshot. I also have a link to the PBIX file below. Any help would be much appreciated!
You should be able to calculate it with something along the lines of:
min PLS = VAR minDate = Calculate(MIN(claendar[{date]) , allselected(calendar)) return Calculate( sum([amount]) , calendar[date] = minDate )
5 Replies
Hi JBI
Sorry can not see the link, but try the below if it works for you as measures.first value = CALCULATE( SELECTEDVALUE( YourTable[Value] ), FIRSTDATE( YourTable[Date] ) )last value = CALCULATE( SELECTEDVALUE( YourTable[Value] ), LASTDATE( YourTable[Date] ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
This also works. Thanks man I appreciate
The issue is with the PLS formulas. The only produce a value if the date calendar is filtered to the flagged date. Which it will never be in a card or in a total.
Add another option in your if statement that checks if
SELECTEDVALUE('Calendar'[Date]) = BLANK()
This should return true if calculated where there is no date dimension filtering.You should be able to calculate it with something along the lines of:
min PLS = VAR minDate = Calculate(MIN(claendar[{date]) , allselected(calendar)) return Calculate( sum([amount]) , calendar[date] = minDate )
