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  ...
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

     

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

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'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]
)
  • yoav20007's avatar
    yoav20007
    Icon for Helper II rankHelper II
    8 years ago

    Thanks Zubair_Muhammad

     

    I try .

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

     

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

      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's avatar
        yoav20007
        Icon for Helper II rankHelper II
        8 years ago

        Thanks again Zubair_Muhammad but ...

        Almost :) 

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