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

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Moody__01 People generally use OneDrive or Box to share PBIX and Excel files. Another way to do it is to just copy the table and paste it as text or save it out to a CSV files and copy and paste that. Don't generally need more than maybe a few dozen rows to recreate things. I may just mock it up because it's interesting but it's a pain doing that.