Forum Discussion

arossello's avatar
arossello
Frequent Visitor
8 years ago
Solved

Problems to replace blank values

Hello everyone,

 

I have a table with a historical quote of currencies, as you can see there are dates with blank values, usually are the holidays, but I need this field to have the value of the last day that has quoted the currency.

 

 

 

 

 

King Regards

 

 

Need Help
  • BobBI's avatar
    BobBI
    8 years ago

    Hi ,

     

    Not sure what extactly you are looking for but as per your screenshot it looks like to want to fill the blanks with previous day value.

     

    To do so , Edit query --> Select  eur_usd column --> Go to "Transform" tab --> select Fill down option from FILL.

     

2 Replies

  • BobBI's avatar
    BobBI
    Resolver III
    8 years ago

    Hi ,

     

    Not sure what extactly you are looking for but as per your screenshot it looks like to want to fill the blanks with previous day value.

     

    To do so , Edit query --> Select  eur_usd column --> Go to "Transform" tab --> select Fill down option from FILL.

     

    • arossello's avatar
      arossello
      Frequent Visitor
      8 years ago

      Hello,

      the problem is that I am working on a calculated table, but thanks to your answer I was able to find a solution in the community, it is the following:

       

      new eur_usd = 
VAR LastNonBlankDate =
     CALCULATE(LASTNONBLANK(cotizaciones[Date];1);
               FILTER(
                ALL(cotizaciones);
                cotizaciones[Date]<=EARLIER(cotizaciones[Date])
                    && NOT( ISBLANK(cotizaciones[eur_usd]) )
                  )
                )
return
    CALCULATE(
        SUM(cotizaciones[eur_usd]);
        FILTER( ALL(cotizaciones); cotizaciones[Date] = LastNonBlankDate)
    )

       As you can see the result is fantastic ...

       

       

      The solution I found in:

       

      http://community.powerbi.com/t5/Desktop/Dax-fill-down-in-formula/td-p/318991

       

      Thank you very much for helping me find the solution