Calculating monthly worth of subscriptions

SanderVeeken — Mon, 30 Jan 2023 11:10:03 GMT

Hello everyone,

I'm trying to calculate the monthly (and/or quarterly) worth of subscriptions. Ideally, I'd be able to slice and dice by date at will. My data model consists of a date table with explicit monthnames and quarters like this:

Date	Year	Quarter	Month	Monthname	Yearmonth	Schoolyear
30-01-2023	2023	1	1	January	202301	22/23

My subscribers table has a lot of columns but the relevant ones are:

Subscriber	startdate	enddate	totallessondays	totalamount	dailyamount
John	01-09-2022	30-09-2022	29	290	10
Bill	01-10-2022	31-10-2022	30	150	5
Henry	15-11-2022	15-01-2023	60	240	4

There is currently no relationship between them.

There are lots of examples on how to calculate a "snapshot table", basically generate a list for each subscriber with every active date, but this is very ineffecient and quickly expands the number of rows to huge numbers. Ideally, I want a dax measure that calculates for every selectable period what the current worth is, so that I can select by day, month, quarter or year.

I got going with this clever post by Reza Rad. I modified the formula because I don't need a check for blank enddates and I wanted the sumx of dailyamount, not a countrows (for active subscriptions). So my formula ended up fairly simple:

Dailytotal =

var _currDate=SELECTEDVALUE('Date'[Date])

return

SUMX(
FILTER(

'Subscribers',

('Subscribers'[enddate]>=_currDate )

&& 'Subscribers'[startdate]<=_currDate

'Subscribers'[dailyamount]

)

It seems to work great, except it won't summarize by month, quarter, year or really anything other than day. Even when I throw Date and Dailytotal in a table, it won't sum but only display it per day. I must be missing something obvious; how do I sum these values?

Re: Calculating monthly worth of subscriptions

johnt75 — Mon, 30 Jan 2023 15:33:03 GMT

You can reuse your daily total measure like

Total for selected dates = SUMX( VALUES( 'Date'[Date]), [Daily total])

Re: Calculating monthly worth of subscriptions

SanderVeeken — Mon, 30 Jan 2023 15:42:08 GMT

Gah, I knew it had to be something simple, thank you so much! I'm sure I tried a variation on that, but I couldn't quite get what I wanted.

topic Calculating monthly worth of subscriptions in DAX Commands and Tips

Calculating monthly worth of subscriptions

Re: Calculating monthly worth of subscriptions

Re: Calculating monthly worth of subscriptions