you're heading in the right direction. You’re leveraging a calculation group to filter your fact table (

) based on the selected granularity, which is correct. Now, to calculate the movement (difference) between periods, you need to reference the previous period's balance dynamically based on the selected granularity.

Here's how you can achieve that:

1. Create a Calculation Group for the Movement Calculation

In addition to the calculation group you’ve created for filtering by date granularity, you should create another calculation group (or extend the existing one) to handle the movement calculation. This will allow you to calculate the difference between the current period's balance and the previous period's balance.

2. Using

PREVIOUSMONTH, PREVIOUSWEEK, PREVIOUSMONTH, PREVIOUSWEEK, PREVIOUSDAY, etc., in combination with CALCULATE to get the balance of the previous period.

Here’s how you can do it:

"Quarterly",
CALCULATE (
SELECTEDMEASURE (),
FILTER (
'trs SMF',
'trs SMF'[dt_q] = PREVIOUSQUARTER ( 'Calendar'[Date] )
)
) - SELECTEDMEASURE(),


"Monthly",
CALCULATE (
SELECTEDMEASURE (),
FILTER (
'trs SMF',
'trs SMF'[dt_m] = PREVIOUSMONTH ( 'Calendar'[Date] )
)
) - SELECTEDMEASURE(),


"Weekly",
CALCULATE (
SELECTEDMEASURE (),
FILTER (
'trs SMF',
'trs SMF'[dt_we] = DATEADD ( 'Calendar'[Date], -7, DAY )
)
) - SELECTEDMEASURE(),


"Daily",
CALCULATE (
SELECTEDMEASURE (),
FILTER (
'trs SMF',
'trs SMF'[dt_day] = PREVIOUSDAY ( 'Calendar'[Date] )
)
) - SELECTEDMEASURE()
)

This solution should give you the ability to dynamically calculate and display the movement between periods based on the user-selected granularity.


Great - thank you Catalin!

Not only it provides scripts but also explanations, pointers - highly useful!

I'm half-way there as in I created another calculation group to calculate the movements and it seems to be working and then I realised that I might have a fundamental flaw with this approach - I mean: is it possible to put on a single visual (table and/or matrix) both - the balances and the movements?

Because when I try to do it in a table visual - once I add the movements calc group - only movements show (but not balances anymore), if I put in the matrix - then they're always nested, i.e., i cannot put them in 2 columns one next to each other - one would display balances and the other would display the movements.

Am I right in thinking that with this approach I cannot achieve that (because the 'movement calc group'  by default always pushes the calculated measure to display only movement but not the balances)?

You're correct in your observation: when you create a calculation group in Power BI, the calculation group essentially replaces the default measure with the logic defined in the selected calculation item. This makes it difficult to display both the original measure (e.g., balances) and the calculated value (e.g., movements) side by side in a single visual using only calculation groups.

Approach and Potential Solutions

To achieve your goal of displaying both balances and movements in two columns side by side, here are a few potential approaches:

1. Duplicate Measures for Balances and Movements

You can create separate measures for balances and movements instead of relying solely on the calculation group. This way, you have control over where each measure is placed in your visual.

• Balance Measure:
   

  Balance = SUM('YourTable'[BalanceColumn])

• Movement Measure:
   

  Movement =
CALCULATE(
[Balance],
DATEADD('Date'[Date], -1, MONTH) // Adjust the period based on your granularity
) - [Balance]


Then, in your table or matrix, you can place both the Balance measure and the Movement measure side by side.

2. Using Calculation Groups with Separate Measures

You can use two calculation groups, but you need to create two separate base measures:

• Base Measure for Balance:
  Balance = SUM('YourTable'[BalanceColumn])

• Base Measure for Movement:
  Movement Calculation =
VAR PreviousPeriod =
CALCULATE(
[Balance],
PREVIOUSMONTH('Date'[Date]) // or PREVIOUSDAY, PREVIOUSQUARTER, etc., based on granularity
)
RETURN
[Balance] - PreviousPeriod


In this case, when you use the calculation groups, you apply one to the Balance measure and another to the Movement Calculation measure. The calculation group should now be used to adjust for different granularities rather than handling the entire calculation.

3. Use Two Different Calculation Groups with SWITCH Function

You can create two separate calculation groups, one for balances and one for movements, and use a SWITCH function within your measures to toggle between the two. Here’s a general idea:

• Balance Calculation Group: Adjusts for granularity (daily, weekly, monthly, etc.)
• Movement Calculation Group: Handles the calculation of the difference between periods.

In your visual, use measures that check the selected calculation group item and apply the appropriate calculation.

• Combined Measure:
  Combined Measure =
SWITCH(
SELECTEDVALUE('CalculationGroup'[CalculationItem]),
"Balance", [Balance],
"Movement", [Movement Calculation],
BLANK()
)


However, this approach can get complex and might still not allow the clean separation into columns that you're looking for.

Conclusion

The most straightforward approach, given the limitations of calculation groups, is to create separate measures for balances and movements. This allows you to place both measures side by side in your visuals without conflicts. Calculation groups are powerful, but they work by replacing measures, so they aren't inherently designed to show multiple calculated results in separate columns simultaneously.

If you decide to go the route of using separate measures, you'll have the flexibility to display both the balance and the movement side by side in any visual without the issues you're currently encountering.

Thanks Cataling again - you're right - it looks like having the 2 measures (balance & movement) is the way to go - thanks for pointing that out!

I marked your initial answer as the answer for the post because it elegantly answered exactly what I asked for in my initial post (I just didn't realize what my faulty assumption were).

Well, I have to be honest and give credit to latest ChatGPT 4o, I was testing it to see if it is able to provide a viable solution based on your (very detailed) description. Even the measures and steps are AI generated.

Answers are pretty solid though.

Thank you