Forum Discussion

yoav20007's avatar
yoav20007
Icon for Helper II rankHelper II
8 years ago
Solved

help with dax calculate column

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

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

     

9 Replies

  • yoav20007's avatar
    yoav20007
    Icon for Helper II rankHelper II
    8 years ago

    how to replace 0 with earlier value?

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

     

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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]
    )
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Hi yoav20007

         

        Try this

         

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