Fill Down in DAX - Calculated column

[Moody__01]

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 able to calculate the average fixing of each currency, but only when i have a fixing for a date. So, for weekend, i dont have anything.

Can someone help me to fill down the last known value for each currency ?

Thanks much, Olivier

[Moody__01 · Accepted solution]

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.

[Moody__01]

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.

[Greg_Deckler]

@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 · Accepted solution]

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.

[Moody__01]

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 · Accepted solution]

@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]

@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]

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

[Greg_Deckler]

@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]

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