How to select a nonblank date

问题:

In power BI, I am computing the percentage difference between Stock price index levels over the last year.

 Ann pch = 

VAR __EarliestValue =   CALCULATE(SUM('Equity Markets (2)'[Value]),
                         DATEADD(LASTDATE('Calendar'[Date]),-1,YEAR))

VAR __LastDateValue = CALCULATE(SUM('Equity Markets (2)'[Value]),
                        LASTDATE('Calendar'[Date]))

RETURN
CALCULATE(
    DIVIDE(__LastDateValue,__EarliestValue) -1)

The above is correct but there is a bug: some dates fall on the weekend, or other non-trading days, in which case I want to select the next nonblank value for __EarliestValue and the previous nonblank value in the case of __LastDateValue.

Could anyone suggest the code to implement this.

I am very much a DAX/Power BI novice. Thank you very much.

Data Sample:

回答1:

I created a slicer based on the column 'Equity Markets(2)'[Date].

note: Do not make the slicer on your calendar date, then you get your "holes"

Then I created a measure with following formula:

Measure = LOOKUPVALUE(EquityMarkets[Value];EquityMarkets[Date]; MAX(EquityMarkets[Date]))/ LOOKUPVALUE(EquityMarkets[Value];EquityMarkets[Date]; MIN(EquityMarkets[Date]))

This measure I show in the card visual. The result is when using the slicer, the calculation is made.