如何选择非空白日期 [英] How to select a nonblank date

问题描述

在Power BI中，我正在计算去年股价指数水平之间的百分比差异。

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)

上面是正确，但有一个错误：某些日期是在周末或其他非交易日，在这种情况下，我想选择 __ EarliestValue 的下一个非空白值和上一个在 __ LastDateValue 情况下为非空白值。

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.

我非常是DAX / Power BI新手。

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

数据示例：

推荐答案

我基于 Equity Markets（2） [日期]列创建了一个切片器。

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

注意：不要在日历日期上做切片器，那样会出现漏洞

然后我创建了具有以下公式的度量：

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.

这篇关于如何选择非空白日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！