topic Calculation groups - timeintelligence and FX in DAX Commands and Tips

Calculation groups - timeintelligence and FX

bgc200 — Mon, 01 Jul 2024 15:46:33 GMT

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 — Tue, 02 Jul 2024 02:37:43 GMT

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.

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 — Tue, 02 Jul 2024 13:11:11 GMT

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?