CODEWITHSUNDEEP
replacenullpowerbipowerpivotdax
Haggan
Haggan

Reputation: 79

DAX POWERPIVOT - Replace blank value with previous

I want to replace the blank value with maximum previous value where "DepositLclCcy" is not blank, i.e. 5 in this case for SEK. How do I write this formula?

Current Result

PositionDate    Currency        DepositLclCcy
2017-04-11      SEK               1
2017-04-11      DKK               3
2017-04-11      EUR               7
2017-04-10      SEK               (blank)
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               5 
2017-04-07      DKK               3
2017-04-07      EUR               5

Desired Result

PositionDate    Currency        DepositLclCcy
2017-04-11      SEK               1
2017-04-11      DKK               3
2017-04-11      EUR               7
2017-04-10      SEK               5 
2017-04-10      DKK               3
2017-04-10      EUR               5
2017-04-07      SEK               5 
2017-04-07      DKK               3
2017-04-07      EUR               5

Upvotes: 0

Views: 2616

Answers (1)

Foxan Ng
Foxan Ng

Reputation: 7151

You can use the following DAX to create the calculated column:

ReplacedDepositLclCcy = 
IF(
    ISBLANK('Table'[DepositLclCcy]),
    CALCULATE(
        FIRSTNONBLANK('Table'[DepositLclCcy], 0),
        FILTER(
            'Table',
            'Table'[PositionDate] < EARLIER('Table'[PositionDate]) &&
            'Table'[Currency] = EARLIER('Table'[Currency]) &&
            'Table'[PositionDate] = LASTDATE('Table'[PositionDate]) &&
            NOT(ISBLANK('Table'[DepositLclCcy]))
        )
    ),
    'Table'[DepositLclCcy]
)

So it basically filters the rows with the max PositionDate which has the same Currency and non-blank DepositLclCcy, and return the DepositLclCcy. It also works for cases where there are consecutive blank values.

Depending on the data type of DepositLclCcy you may need to change ISBLANK('Table'[DepositLclCcy]) to 'Table'[DepositLclCcy] = "".

Results:

results

Upvotes: 1

Related Questions