topic Calculating weekly churn volume against monthly base volume in DAX Commands and Tips

Calculating weekly churn volume against monthly base volume

Anonymous — Mon, 14 Jul 2025 23:59:15 GMT

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

Re: Calculating weekly churn volume against monthly base volume

BeaBF — Tue, 15 Jul 2025 07:48:44 GMT

Anonymous 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!

Re: Calculating weekly churn volume against monthly base volume

FBergamaschi — Tue, 15 Jul 2025 09:15:25 GMT

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

Re: Calculating weekly churn volume against monthly base volume

Anonymous — Tue, 15 Jul 2025 22:39:39 GMT

Thank you so much, this worked!

Re: Calculating weekly churn volume against monthly base volume

Anonymous — Tue, 15 Jul 2025 22:41:43 GMT

Great solution, thank you!