Blog Post

Power BI Community Blog
5 MIN READ

Using EARLIER() with a SMART INDEX.

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago
Many times, we need to find a value from a different row than the one we are on, in order to use that value for a formula. In Excel the engine knows that cell C1 is in the row above cell C2 and thus ...
Updated 6 years ago
Version 6.0
how to
Nathaniel_C's avatar
Community Champion
Joined August 03, 2019
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.
marcorusso's avatar
marcorusso
Most Valuable Professional
6 years ago

Using variables it would be better to avoid EARLIER, which was necessary when DAX did not have variables.

 

Hourly Difference = 
VAR PreviousSmartIndex = Meter[Smart Index] - 1
VAR firstread =
    CALCULATE (
        MAX ( Meter[Hourly Reading] ),
        REMOVEFILTERS ( Meter ),
        Meter[Smart Index] = PreviousSmartIndex
    ) //finds the previous reading using Smart Index, so essentially sorting and filtering here 
VAR currentread = Meter[Hourly Reading] //finds the current reading 
VAR result =
    IF (
        Meter[Smart Index] = 0,
        0,
        currentread - firstread
    ) // 3 possible values, 0, blank or the difference. Here either 0 or the difference, the next line the difference or blank
RETURN
    IF (
        Meter[Unique ID]
            = CALCULATE (
                MAX ( Meter[Unique ID] ),
                REMOVEFILTERS ( Meter ),
                Meter[Smart Index] = PreviousSmartIndex
            ),
        result
    )