How calculate the ‘change’ month to month to drive the Unit Rollforward in dax

[Gopal_PV]

Hi Folks,

I am connecting from snowflake Datamart to Power BI Using View.  I have retriving Created_dt, Year,Month,ID,Name, sum(Units) from the view.
It is subscription based business. I want BOP, New Units, Expansion, Contraction,Churn, EOP
Not able to understand the below logic. Can you please help in this .

Thank in Advance.

BOP… # units at the Beginning of a Period

+ New Units… # units added in the period that were not in the prior period

Ie. If a Client was activated in Jun ’25… May ’25 should have 0, and June ’25 should have >0.. New Units

+ Expansion… # units added between periods with an Existing Customer

June ’25 – 100 units

May ’25 – 90 units

Expansion = 10 units

- Contraction… # units reduced between periods with an Existing Customer

June ’25 – 80 units

May ’25 – 100 units

Contraction = (20) units

- Churn… # units lost between periods, ending period must be 0

June ’25 – 0 units… this must be Zero to be categorized as Churn

May ’25 – 100 units

Chrun – (100) units

EOP = BOP + New + Expansion – Contraction – Churn

DATA:

Created_dt	Year_Created	Month_Created	ID	Name	UNITS
6/14/2022	2022	6	1175	Planet Entertainment	52973
7/26/2022	2022	7	1185	sony Entertainement	5758
9/19/2022	2022	9	204	Amazon Entertainement	27594
10/26/2022	2022	10	1224	Netflix	25692
10/30/2022	2022	10	1228	Z5	0
1/4/2023	2023	1	1282	Aha	22475
3/13/2023	2023	3	1336	JioHotstar	4280
4/3/2023	2023	4	1373	MX Player	0
6/9/2022	2022	6	1172	Voot	26240
8/1/2022	2022	8	1189	Youtube	204104
10/25/2022	2022	10	1221	Eros Now	36778
10/30/2022	2022	10	1227	Alt Balaji	448957
1/19/2023	2023	1	1111	Discovery ++	232743

[Greg_Deckler · Accepted solution]

@Gopal_PV Maybe something like the following:

Subscriptions = 
ADDCOLUMNS(
    'YourView',
    "Period", DATE([Year_Created], [Month_Created], 1)
)

SubscriptionsWithPrev = 
VAR Current = SELECTCOLUMNS('Subscriptions', "ID", [ID], "Period", [Period], "Units", [UNITS])
RETURN
    GENERATE(
        Current,
        VAR PrevPeriod = EDATE([Period], -1)
        RETURN
            FILTER(
                'Subscriptions',
                'Subscriptions'[ID] = [ID] &&
                'Subscriptions'[Period] = PrevPeriod
            )
    )

BOP = 
SUMX(
    FILTER(
        'Subscriptions',
        'Subscriptions'[Period] = 
            MINX('Subscriptions', EDATE('Subscriptions'[Period], 1))
    ),
    'Subscriptions'[UNITS]
)


New Units =
SUMX(
    FILTER(
        'Subscriptions',
        NOT (
            'Subscriptions'[ID] 
            IN 
            SELECTCOLUMNS(
                FILTER(
                    'Subscriptions',
                    'Subscriptions'[Period] = EDATE(MAX('Subscriptions'[Period]), -1)
                ),
                "ID", 'Subscriptions'[ID]
            )
        )
        &&
        'Subscriptions'[Period] = MAX('Subscriptions'[Period])
    ),
    'Subscriptions'[UNITS]
)



Expansion =
SUMX(
    FILTER(
        'Subscriptions',
        VAR CurrentPeriod = 'Subscriptions'[Period]
        VAR PrevPeriod = EDATE(CurrentPeriod, -1)
        VAR CurrentUnits = 'Subscriptions'[UNITS]
        VAR PrevUnits =
            SUMX(
                FILTER(
                    'Subscriptions',
                    'Subscriptions'[Period] = PrevPeriod &&
                    'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
                ),
                'Subscriptions'[UNITS]
            )
        RETURN
            CurrentUnits > PrevUnits && PrevUnits > 0
    ),
    'Subscriptions'[UNITS] -
    SUMX(
        FILTER(
            'Subscriptions',
            'Subscriptions'[Period] = EDATE('Subscriptions'[Period], -1) &&
            'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
        ),
        'Subscriptions'[UNITS]
    )
)


Contraction =
SUMX(
    FILTER(
        'Subscriptions',
        VAR CurrentPeriod = 'Subscriptions'[Period]
        VAR PrevPeriod = EDATE(CurrentPeriod, -1)
        VAR CurrentUnits = 'Subscriptions'[UNITS]
        VAR PrevUnits =
            SUMX(
                FILTER(
                    'Subscriptions',
                    'Subscriptions'[Period] = PrevPeriod &&
                    'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
                ),
                'Subscriptions'[UNITS]
            )
        RETURN
            CurrentUnits < PrevUnits && CurrentUnits > 0
    ),
    SUMX(
        FILTER(
            'Subscriptions',
            'Subscriptions'[Period] = EDATE('Subscriptions'[Period], -1) &&
            'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
        ),
        'Subscriptions'[UNITS]
    ) - 'Subscriptions'[UNITS]
)


Churn =
SUMX(
    FILTER(
        'Subscriptions',
        'Subscriptions'[UNITS] = 0 &&
        SUMX(
            FILTER(
                'Subscriptions',
                'Subscriptions'[Period] = EDATE('Subscriptions'[Period], -1) &&
                'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
            ),
            'Subscriptions'[UNITS]
        ) > 0
    ),
    SUMX(
        FILTER(
            'Subscriptions',
            'Subscriptions'[Period] = EDATE('Subscriptions'[Period], -1) &&
            'Subscriptions'[ID] = EARLIER('Subscriptions'[ID])
        ),
        'Subscriptions'[UNITS]
    )
)


EOP = [BOP] + [New Units] + [Expansion] - [Contraction] - [Churn]

[v-aatheeque]

Hi @Gopal_PV 

Just checking in regarding the solution we shared earlier by @Gopal_PV for calculating BOP, New Units, Expansion, Contraction, Churn, and EOP using DAX in Power BI, based on your Snowflake view data.

Could you please confirm if the approach helped in resolving your query and if the metrics are now reflecting as expected in your report?

If you’re facing any challenges implementing it or need further clarification, feel free to share the details  we’ll be happy to assist further.

Looking forward to your response!