Forum Discussion

bgc200's avatar
bgc200
Regular Visitor
2 years ago

Calculation groups - timeintelligence and FX

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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      bgc200
      Regular Visitor
      2 years ago

      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?