Forum Discussion

Moody__01's avatar
Moody__01
Helper I
2 years ago
Solved

Fill Down in DAX - Calculated column

Hi everyone,

I try to fill down in a calculated column the last know value for each currency.

I have to calculate for each month the average value of all the fixing for each currency. So far, i've able to calculate the average fixing of each currency, but only when i have a fixing for a date. So, for weekend, i dont have anything.

Can someone help me to fill down the last known value for each currency ?

 

Thanks much, Olivier

Need Help
  • Moody__01's avatar
    Moody__01
    2 years ago

    i did it ! i finally create a mesure giving me the year & month of each date in my fixing table

     

    And now i run a look up value based on this value, to know what is the average for this month.

    my calculated column for people who may need the same.

    fxextended =
    var _Ccy = ECB_fixingperday[Ccy.Ccy]
    var YYYYMM = YEAR(ECB_fixingperday[Count.Dates])&MONTH(ECB_fixingperday[Count.Dates])
    RETURN LOOKUPVALUE(ECB_Fixing[FX_MonthAverage (Column)],ECB_Fixing[Currency],_Ccy,ECB_Fixing[YYYYMM],YYYYMM)

     

    now i have the right fixing to go forward !

     

    Best, O.

  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Moody__01 If you can provide sample data as text I can mock it up and get it exactly correct. However, guessing at what is going wrong, try this:

    Column = 
  VAR __Date = [Count Dates]
  VAR __Currency = [CcyCcy]
  VAR __PrevDate = MAXX(FILTER( 'Table', [Count Dates] < __Date && [CcyCcy] = __Currency && [FX_M <> BLANK() ), [Count Dates])
  VAR __Value = MAXX( FILTER( 'Table', [Count Dates] = __PrevDate && [CcyCcy] = __Currency && [FX_M <> BLANK()), [FX_M])
  VAR __Result = IF( [FX_M] = BLANK(), __Value, [FX_M] )
RETURN
  __Result

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Moody__01 Try this:

    Column = 
  VAR __Date = [Count Dates]
  VAR __Currency = [CcyCcy]
  VAR __PrevDate = MAXX(FILTER( 'Table', [Count Dates] < __Date && [CcyCcy] = __Currency), [Count Dates])
  VAR __Result = MAXX( FILTER( 'Table', [Count Dates] = __PrevDate && [CcyCcy] = __Currency), [FX_M])
RETURN
  __Result
    • Moody__01's avatar
      Moody__01
      Helper I
      2 years ago

      i did it ! i finally create a mesure giving me the year & month of each date in my fixing table

       

      And now i run a look up value based on this value, to know what is the average for this month.

      my calculated column for people who may need the same.

      fxextended =
      var _Ccy = ECB_fixingperday[Ccy.Ccy]
      var YYYYMM = YEAR(ECB_fixingperday[Count.Dates])&MONTH(ECB_fixingperday[Count.Dates])
      RETURN LOOKUPVALUE(ECB_Fixing[FX_MonthAverage (Column)],ECB_Fixing[Currency],_Ccy,ECB_Fixing[YYYYMM],YYYYMM)

       

      now i have the right fixing to go forward !

       

      Best, O.

    • Moody__01's avatar
      Moody__01
      Helper I
      2 years ago

      Hi Greg_Deckler ,

      first thing first, thanks much to taking time to have a look at my issue.

      I did what you suggested, and i have a misalignement.

       So i updated the return for 

      RETURN IF(ISBLANK(ECB_fixingperday[FX_M]),__Result,ECB_fixingperday[FX_M]). Now i have this 

       

      sot the formula you shared with me works for the next cells after a non blank value. Anyway to get it populated until the next non blank cell ? in the above case, chf fixing of 06.01 to be filled down for 7.01 and 8.01

       

      thanks, Olivier

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        Moody__01 If you can provide sample data as text I can mock it up and get it exactly correct. However, guessing at what is going wrong, try this:

        Column = 
  VAR __Date = [Count Dates]
  VAR __Currency = [CcyCcy]
  VAR __PrevDate = MAXX(FILTER( 'Table', [Count Dates] < __Date && [CcyCcy] = __Currency && [FX_M <> BLANK() ), [Count Dates])
  VAR __Value = MAXX( FILTER( 'Table', [Count Dates] = __PrevDate && [CcyCcy] = __Currency && [FX_M <> BLANK()), [FX_M])
  VAR __Result = IF( [FX_M] = BLANK(), __Value, [FX_M] )
RETURN
  __Result
  • Moody__01's avatar
    Moody__01
    Helper I
    2 years ago

    I realized another issue by applying your solution because i wrongly express my need so i believe i own you a bit of background 🙂 !
    My column (FX_M) is a calculated column where i actually do a look up in another table where i calculate the average fixing over a month (based on the number of fixing during that month).

    In my table Fixingperday, i try to get, for each single calendar day of a month, what is the related FX average.

     

    in my table fixingperday,  i apply the below formula to calculate the column FX_Month:

     
    FX_M =
    Var _ccy = ECB_fixingperday[Ccy.Ccy]
    Var _date = ECB_fixingperday[Count.Dates]
    RETURN LOOKUPVALUE(ECB_Fixing[FX_MonthAverage(Column)],ECB_Fixing[Currency],_ccy,ECB_Fixing[Date],_date).
    This is giving me only a result where there is a match. However, for days over a month which dont have fixing (i. weekend), i have a blank value.
     
    By applying your suggestion, i apply the last know value.
    For example on 1st of july, i fill the cell with the value of 30/06:
    In real, i would like here to get the fx average for july instead. 

    The solution may be to refer to the month & year of the day (?), but i'm too new in BI to understand byself how..

     

    Thanks for your help.

    Best, O.