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?
