Forum Discussion

Moody__01's avatar
Moody__01
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity 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
      Icon for Helper I rankHelper I
      2 years ago

      Greg_Deckler i would be interesting to see how fill down on Dax. 

      I made a bi i can share with you.  im happy to learn from you how to fill down based the last fixing of each currency.

      Thanks much, O.

       

      • Moody__01's avatar
        Moody__01
        Icon for Helper I rankHelper I
        2 years ago

        i feel a bit stupid as i cannot attach my file in my message... neither the pbix or excel file 😕