Calculating weekly churn volume against monthly base volume

[SecretChimpanze]

Hi - I have two tables that have been appended: Churn with volumes in week and month fields and Base with data only in the month field (week field is all blank). Reason is I need 2 years of data and the base is too big to bring in at a weekly level.

I need to calculate a churn rate: weekly churn / that month's base (or closest month as some weeks will be across two months)

This is what the data currently looks like

[BeaBF · Accepted solution]

@SecretChimpanze Hi! Create a separate Base Table and call it BaseReference.

Then create a relationship between your combined table and BaseReference through trde_mth.

Create a Churn Rate Measure like:

Churn Rate (%) =
DIVIDE(
SUM(Combined[Churn Vol]),
RELATED(BaseReference[Opening Base])
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[FBergamaschi · Accepted solution]

Keep the two tables separated (I call then Churn Monthly Data and Churn Weekly Data just as an example)

Create a calendar calculate table

Cal = CALENDARAUTO()

Connect the Calendar Date column to the trade_month column of both tables

Create two measures

WeeklyChurn = SUM ( 'Churn Weekly Data'[ChurnVol] )

Monthly Churn =

VAR VisibleDate = MAX ( Cal[Date] )

VAR MonthVisibleDate = DATE ( YEAR ( VisibleDate ), MONTH( VisibleDate ), 1 )

VAR WeekChurn = [WeeklyChurn]

RETURN

IF (
     [WeeklyChurn] <>0,

    CALCULATE(
             SUM('Churn Monthly Data'[Churn]),
             REMOVEFILTERS( ),
             Cal[Date] = MonthVisibleDate )
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[SecretChimpanze]

Thank you so much, this worked!

[BeaBF · Accepted solution]

@SecretChimpanze Hi! Create a separate Base Table and call it BaseReference.

Then create a relationship between your combined table and BaseReference through trde_mth.

Create a Churn Rate Measure like:

Churn Rate (%) =
DIVIDE(
SUM(Combined[Churn Vol]),
RELATED(BaseReference[Opening Base])
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[SecretChimpanze]

Great solution, thank you!