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

Moody__01's avatar
Moody__01
Helper I
2 years ago

my place doesnt allow me to share any one drive link.. but i was on my way to write to you. The formula you shared is working. I was trying to collect the value from another table (where i can miss several days in a row).

FX Spot (vs USD) = 
  VAR __Date = [Date]
  VAR __Currency = [Currency]
  VAR __PrevDate = MAXX(FILTER(FixingPerDay, [Date] < __Date && [Currency] = __Currency && [_Fixing] <> BLANK() ), [Date])
  VAR __Value = MAXX( FILTER(FixingPerDay, [Date] = __PrevDate && [Currency] = __Currency && [_Fixing] <> BLANK()), [_Fixing])
  VAR __Result = IF( [_Fixing] = BLANK(), __Value, [_Fixing] )
RETURN
  __Result

 

I finally managed to get it done, all in dax i.o Query M and i also understand the logic 🙂

Perfect combo, thanks  Greg_Deckler