Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
You can reuse your daily total measure like
Total for selected dates = SUMX( VALUES( 'Date'[Date]), [Daily total])
You can reuse your daily total measure like
Total for selected dates = SUMX( VALUES( 'Date'[Date]), [Daily total])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |