help with dax calculate column

[yoav20007]

I need to create calculate column  that add the last day rate if it is empty. I need that all date have rates .

Thanks !!!

date                rate      all rates

1/11/17           5

2/11/17           

3/11/17

4/11/17           4

5/11/17          

6/11/17          4.8

[Zubair_Muhammad]

Hi @yoav20007

Try this calculated column

=
VAR Daybefore =
    PREVIOUSDAY ( Table1[date] )
RETURN
    IF (
        ISBLANK ( Table1[Rates] ),
        CALCULATE (
            VALUES ( Table1[Rates] ),
            FILTER ( ALL ( Table1 ), Table1[date] = Daybefore )
        ),
        Table1[Rates]
    )

[yoav20007]

Thanks @Zubair_Muhammad

But when i have more than 1 day blanks rates it's dosn't work.

I need to repeat the same rate

[Zubair_Muhammad]

Hi @yoav20007

Try this

=
IF (
    ISBLANK ( Table1[Rates] ),
    CALCULATE (
        LASTNONBLANK ( Table1[Rates], Table1[Rates] ),
        FILTER ( Table1, Table1[date] < EARLIER ( Table1[date] ) )
    ),
    Table1[Rates]
)

[yoav20007]

Thanks @Zubair_Muhammad

I try .

But it dosn't works. That's the result . the same number for all of them. 

[Zubair_Muhammad]

@yoav20007

Hmmm!!!! May be this one

=
IF (
    ISBLANK ( Table1[Rates] ),
    CALCULATE (
        LASTNONBLANK ( Table1[Rates], Table1[Rates] ),
        FILTER (
            Table1,
            Table1[date] < EARLIER ( Table1[date] )
                && Table1[date] > MIN ( Table1[date] )
        )
    ),
    Table1[Rates]
)

[yoav20007]

Thanks again @Zubair_Muhammad but ...

Almost 🙂 

that's the result. after the first time it dosn't works.

[MFelix · Accepted solution]

Hi @yoav20007,

Why don't you do this in the query editor.

Insert a custom column with the following syntax: 

All Rates = [Rate]

Then right click on the new column header and select fill down final result below:

Regards,

MFelix

[yoav20007]

Hi @MFelix Thank you.

Because it to simple and i didn't think about that 🙂 

[yoav20007]

how to replace 0 with earlier value?

test = IF(ISBLANK(sales[rate])=TRUE(),0,sales[rate])