If unfiltered, display "most recent" (not necessarily date-related) non-blank value

[Anonymous]

Hi all, hopefully a simple question here. I have a percent value for each year-quarter pair, shown below. Users are forced to filter on a single year, and can optionally filter one or more quarters. If one quarter is chosen, the VI% is correct. If multiple are selected, they sum, which isn't correct. 

What we need is for the "latest" (year-quarter) value we have to display if quarter has more than one value.

E.g. if Q1 is selected, display 67%, if Q2 is selected display 2.45%, if more than one is selected, display 2.45%. If, down the line, Q3 is added, multiple selections should display that value.

The first part is obvious: 

IF(HASONEVALUE(table[YearQuarterKey]), VI%, TBD)

but I'm unsure how to write the expression that would return the rest. I tried to pull the actual latest YearQuarterKey value with:

IF(HASONEVALUE(table[YearQuarterKey]),VI%, CALCULATE(VI%, table[YearQuarterKey] = MAX(YearQuarterKey)))

but that didn't work. I also tried LASTNONBLANK and LASTNONBLANKVALUE but wans't able to figure out the syntax to get it to do what I need. 

Any advice?

[parry2k · Accepted solution]

@Anonymous check if it helps Avoid pitfalls when using LASTNONBLANKVALUE and LASTNONBLANK functions - leads to incorrect results - YouTube

[parry2k]

@Anonymous could you provide more details, on how your data looks like, and what are the calculations? Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @parry2k, thanks for the repsonse. For the purposes of the example, I believe all the fact data we need is basically as provided. There are not currently other measures in this table, other than the one I'm trying to create.

Fact_table

Key 1	Key 2	YearQuarterKey	VI%
7096	7105	20231	x%
7096	7015	20232	y%

This fact table is connected to my Dim.Date by a bridge that contains YearQuarterKey, and the Year and Quarter slicers I mentioned are controlled through the Year and Quarter columns in that table. 

For a given parent and child, the data looks as below. When the Quarter field is in the matrix as an example, you can see the two VI% values.

When the quarter hierarchy is removed, it looks like this, which is how it will look to the user. In this case, both quarters 1 and 2 are selected, but I want that value to display as 2.5%, since that is our "latest" VI% value, rather than summing to 70.1%