Calculation groups - timeintelligence and FX

[bgc200]

Hello,

I'm trying to creata a model to calculate 2 things:

- Timeintelligence functions

- Currency translations

I used to do this through switches, where I have 2 FX types (source & actual) and 2 timeintelligence periods (Current and Prior year).

The switch I did by adding the time filtering through relationship of the connected DimDate.

And the FX I did by creating a calculated column Actual, which is just Value * Related FX rate in the connection DimCurrency table.

This results in 2 value columns, the source (which is the Value column) and the actual (which is the FX translated value).

So I go from Value -> Currency selector Switch -> Time selector Switch -> Result

I rewrote this partially as calculation groups.

I removed the Time selector switch and added the calculation items in SelectTime:

 CALCULATE ( [Sum value], DATESBETWEEN ( DimDate[idDate], FIRSTDATE ( DimPeriod[cdmPeriodBeg] ), LASTDATE ( DimPeriod[cdmPeriodEnd] ) )

 I use a disconnected periods table to prefilter the dimdate, due to the overlapping time periods, requiring me to reuse some idDates in other tables.

This calculation works, by changing indeed the result to the correct time period.

Now my questions is, can I also add the FX calculation as a calculation group?

I need to be able to switch between both, i.e. Actual Prior year, or Source Current year, etc.

[Anonymous]

Hi @bgc200 ,

Based on the description, it is possible to create a new calculation group for fx calculations. Then, define calculation items for fx types.

You can view the following documents to learn more information.

Calculation groups in Analysis Services tabular models | Microsoft Learn

Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bgc200]

thanks for the reply!

Does this mean I can indeed have 2 different groups, 1 for the FX calculation and 1 for the time intelligence filtering?

Does my current solution with the FX translation as different calculated columns and then a separate sum measure for both work in this context?

Or do I need to do the FX calculation in the calculation group?

Lastly, what's the correct precedence order? 

First the time intelligence to filter my values table and then apply the FX translation calculation through a SUMX of Value * Related FXrate?