Calculated column formula

[luirib]

I have 3 tables, the fist is the FactTable, the second is the Calendar table and the third is the DailyFx table, which lists the exchange rate for every single day of the year. I am trying to come up with a calculated column in the FactTable that for every row it will give me the average exchange rate for the given month (for example, this column will show the same number for every row in November 2015, the same number for every row in December 2015, and so on).

The Fact table has the following columns:
Date
Amount

The Daily Fx table has the following columns:
Date
Rate

The calendar table has the following columns:
Date
Month Name

I have an active relationship between the FactTable and the DailyFx table on the Date column and an inactive relationship on the Date column between the Calendar and DailyFx table.

This is the function that I have but it is not quite doing the job:
=
CALCULATE (
AVERAGE ( DailyFx[Rate] ),
ALL ( Calendar ),
FILTER (
Calendar,
DATESBETWEEN (
Calendar[Date],
EOMONTH ( FactTable[Date], 0 ),
EOMONTH ( FactTable[Date], -1 ) + 1
)
)
)

Please point me to the right direction. Thank you.

 

[Ozeroth]

Hi there,
First if all I would suggest changing your relationships so that both the Fact Table and Daily FX tables are related to the Calendar table with active relationships, as those tables are both effectively "fact tables".

Then make sure Calendar table is marked as date table.

Then in your Fact table you could add a calc column:

=
CALCULATE (
    AVERAGE ( DailyFX[Rate] ),
    PARALLELPERIOD ( Calendar[Date], 0, MONTH )
)

PARALLELPERIOD does the same thing you were aiming for with EOMONTH but is a bit easier to read and works if relationships are set up to a common Calendar table.

 

[luirib]

Thanks Ozeroth. I had a bit of a mess on my relationships, so once I fixed that and used your suggestion it is working like clockwork.

Hi there,
First if all I would suggest changing your relationships so that both the Fact Table and Daily FX tables are related to the Calendar table with active relationships, as those tables are both effectively "fact tables".

Then make sure Calendar table is marked as date table.

Then in your Fact table you could add a calc column:

=
CALCULATE (
    AVERAGE ( DailyFX[Rate] ),
    PARALLELPERIOD ( Calendar[Date], 0, MONTH )
)

PARALLELPERIOD does the same thing you were aiming for with EOMONTH but is a bit easier to read and works if relationships are set up to a common Calendar table.