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

     

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]
    )
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]
)