Forum Discussion

Moody__01's avatar
Moody__01
Icon for Helper I rankHelper I
3 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 ...
Need Help
  • Moody__01's avatar
    Moody__01
    3 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
    3 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
Icon for Helper I rankHelper I
3 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.