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