Followers % Chg when data is accumulated - Max/Last Date

[Alpesh]

Trying to calculate % Chg on Social Media followers however, the data that is given is accumulated:

How can this be done for last quarter vs. previous closed qtr? I have a calendar table with date offsets, however, the measure is just summing the total for the entire quarter and not pickng the last/max date in the last quarter....?

Brand	Network	Date	Followers
XYZ	Facebook	01/01/2024	15000
XYZ	Facebook	02/01/2024	15200
XYZ	Facebook	03/01/2024	15300
XYZ	Instagram	01/01/2024	22500
XYZ	Instagram	02/01/2024	25000

[suparnababu8 · Accepted solution]

Hi @Alpesh 

we need to adjust your DAX formulas to ensure they correctly calculate the percentage changes and follower counts for the specified periods. Here’s how you can do it:

Calculation Groups

1. Previous Quarter: 

Previous Quarter = 
CALCULATE(
    SELECTEDMEASURE(), 
    'Calendar'[CurQuarterOffset] = -1
)

• Current Quarter -2:

Current Quarter -2 = 
CALCULATE(
    SELECTEDMEASURE(), 
    'Calendar'[CurQuarterOffset] = -2
)

•  
• % Chg Qtr vs. YAG: 

% Chg Qtr vs. YAG = 
DIVIDE(
    CALCULATE(
        SELECTEDMEASURE(), 
        'Dynamic Calculation Group'[Dynamic Periods] = "Previous Quarter"
    ) - 
    CALCULATE(
        SELECTEDMEASURE(), 
        'Dynamic Calculation Group'[Dynamic Periods] = "Closed Quarter YAG"
    ),
    CALCULATE(
        SELECTEDMEASURE(), 
        'Dynamic Calculation Group'[Dynamic Periods] = "Closed Quarter YAG"
    )
)

• Previous Month:

Previous Month = 
CALCULATE(
    SELECTEDMEASURE(), 
    'Calendar'[CurMonthOffset] = -1
)

These measures should help you calculate the required metrics and percentage changes for your social media followers. 

1.  

[Alpesh]

Here's the excel file: https://docs.google.com/spreadsheets/d/1NuK9mEQoIWvocDh5GI8HSAnH4Ent1OnO/edit?usp=sharing&ouid=10320...

Requirement is:

Last Qtr Closed: Sum of all Networks & breakdown by network: % Chg vs. previous closed qtr, % chg vs. closed qtr YAG. We'd also need to display the followers count at the last day of the closed quarter

Last Month Closed: Sum of all Networks & breakdown by network: % Chg vs. previous closed month, % chg vs. closed month YAG. We'd also need to display the followers count at the last day of the closed month

Sum of all Networks & breakdown by network: % Chg YTD closed vs. YTD YAG closed

Slicer is used to create brand reports

I want to use calculation groups in conjuction (as I have lots of other social media metrics - engagements, likes, shares, saves.... already with calculation groups for the % Chg)

Formulas used for calculation group for the other social media metrics:

Previous Quarter = CALCULATE(SELECTEDMEASURE(), 'Calendar'[CurQuarterOffset] = -1)

Current Quarter -2 = CALCULATE(SELECTEDMEASURE(), 'Calendar'[CurQuarterOffset] = -2)

% Chg Qtr vs. YAG = DIVIDE(

                            CALCULATE(SELECTEDMEASURE(), 'Dynamic Calculation Group'[Dynamic Periods] = "Previous Quarter") -

                            CALCULATE(SELECTEDMEASURE(), 'Dynamic Calculation Group'[Dynamic Periods] = "Closed Quarter YAG"),

                            CALCULATE(SELECTEDMEASURE(), 'Dynamic Calculation Group'[Dynamic Periods] = "Closed Quarter YAG"))

Previous Month = CALCULATE(SELECTEDMEASURE(), 'Calendar'[CurMonthOffset] = -1)

[Ashish_Mathur]

Hi,

Given this data, my

my result is

PBI file attached.

Hope this helps.

[lbendlin]

Not enough sample data.  You need to include the closing balance of the preceding months  (Dec 2023 in this case.)