Difference Between Oldest and Latest Value

[JBI]

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!

@v-frfei-msft 

[Mariusz]

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.

[JBI]

This also works. Thanks man I appreciate

[tex628]

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. 

[JBI]

Hi @tex628,

SELECTEDVALUE doesn't work since it requires a column as a parameter and in my example the flag is created by a measure. 

Do you know how to solve this problem without using measures to flag which two columns to use?

Thanks!

[tex628 · Accepted solution]

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
)