Forum Discussion
Converting timed absolute snapshots into a running total
Hi Guys,
I've been browsing this forum and learning powerbi at an incredible pace thanks to the help of this community. The questions that I had usually matched pretty well with what I needed, however I've hit an issue for which I can only find pieces of the puzzle on the forum and/or the internet. Allow me to explain:
I have a data set that holds a snapshot of the subscriptions of our customers. Whenever the subscscription changes, a new snapshot is written to the DB with the date of creation and the absolute number of licenses the customer has, this is split up in 2 tables:
InvoiceMetric, holding the date of the transaction and the customer ID:
| Id | Date | CustomerId |
| 1 | 2022-04-22 10:50:58.7822871 +00:00 | 44 |
| 2 | 2022-05-22 13:44:08.0962072 +00:00 | 17 |
| 3 | 2022-06-24 09:29:44.7250042 +00:00 | 22 |
| 4 | 2022-07-07 12:45:20.6973675 +00:00 | 7 |
| 5 | 2022-08-17 06:46:11.1742613 +00:00 | 8 |
| 6 | 2022-09-17 06:46:17.1613008 +00:00 | 9 |
| 7 | 2022-10-17 06:46:22.9421034 +00:00 | 10 |
| 8 | 2022-11-17 06:46:29.8302456 +00:00 | 11 |
| 9 | 2022-12-17 06:46:36.4386224 +00:00 | 18 |
| 10 | 2023-01-17 06:46:59.3789473 +00:00 | 25 |
| 11 | 2023-02-17 06:47:06.8450557 +00:00 | 28 |
| 12 | 2023-02-18 07:17:01.8704410 +00:00 | 7 |
| 13 | 2023-03-17 06:47:14.2703319 +00:00 | 30 |
InvoiceMetricDetail, having a 1:* relation with InvoiceMetric[Id] through the InvoiceMetricId column. Each row lists the license count per generation of machine and per licensed module (DeviceCount).
| Id | MachineGenerationId | Module | DeviceCount | InvoiceMetricId |
| 1 | 1 | 0 | 5 | 1 |
| 2 | 1 | 1 | 5 | 1 |
| 3 | 1 | 2 | 5 | 1 |
| 4 | 2 | 0 | 5 | 1 |
| 5 | 2 | 1 | 5 | 1 |
| 6 | 2 | 2 | 5 | 1 |
| 7 | 1 | 0 | 5 | 2 |
| 8 | 1 | 0 | 35 | 2 |
| 9 | 1 | 0 | 8 | 3 |
| 10 | 2 | 0 | 8 | 3 |
| 11 | 1 | 0 | 4 | 3 |
| 12 | 2 | 0 | 28 | 4 |
| 13 | 1 | 0 | 20 | 4 |
| 14 | 1 | 0 | 24 | 5 |
| 15 | 1 | 2 | 24 | 5 |
| 16 | 2 | 0 | 7 | 5 |
| 17 | 2 | 2 | 7 | 5 |
| 18 | 3 | 0 | 18 | 5 |
| 19 | 3 | 2 | 18 | 5 |
| 20 | 1 | 0 | 8 | 6 |
| 21 | 1 | 0 | 60 | 7 |
| 22 | 2 | 0 | 48 | 7 |
| 23 | 1 | 0 | 38 | 8 |
| 24 | 1 | 0 | 9 | 9 |
| 25 | 1 | 0 | 9 | 10 |
| 26 | 1 | 0 | 12 | 11 |
| 27 | 2 | 0 | 22 | 11 |
| 28 | 2 | 0 | 0 | 12 |
| 29 | 1 | 0 | 0 | 12 |
| 30 | 1 | 0 | 3 | 13 |
| 31 | 2 | 0 | 26 | 13 |
The graph I would like to plot now is a bar graph, with legend the module, x-axis the date (month/year) and y-axis the running total of ACTIVE licenses at that point in time. The problem I have is that each row in InvoiceMetricDetail holds an absolute value. For example, customer 7 has subscribed July 7th of 2022 (indicated in green above), and unfortunately unsubscribed Feb 18th of 2023 (indicated in blue above).
If I were to use a classic RunningTotal, that specific transaction would result in no change while in fact it is a negative change of 28 and 20 respectively. This example now only shows a small subset of the actual data of course, so in practice each customer has multiple transactions some going up, others going down, but the principle is the same, only the snapshot is saved to the DB, not the delta.
How can I achieve the graph I want? This is my current strategy, I think I need help with steps 3 and 4 (or a completely different strategy 😅😞
1. merge the 2 tables and clean up the dates (remove time) so I have everything combined
2. Grouped this table by module and added a runningtotal per module (thanks to BI Gorilla on YT: https://www.youtube.com/watch?v=ShnWkb6e0jE&t=64s)
3. Added a calendar table with firstdate/lastdate and added a column with a lookupvalue, in a first attempt to try to get rid of 'holes' in the data, however this is giving me an error 'A table of multiple values was supplied where a single value was expected'. I was trying to follow this answer: https://community.powerbi.com/t5/Desktop/fillin-gaps/m-p/2210219#M807785 but I have multiple entries per date and thus can't just use this solution as is.
4. What I need to do (I think) after I solved previous issue, is probably try to add a column with 'delta compared to previous' or alike and insert that between step 1 and 2 to give me a correct runningtotal?
Any and all help is greatly appreciated.
The following graph is an example and does not use the data above, I hope it shows the basic idea though:
What I have so far (with the 'holes'):
What it should look like if I can fix the holes:
Thanks!
Nic.
2 Replies
- lbendlin
Super User
Your sample data is missing the dates table. What is your expected granularity? Do you want to show the status by customer by month? Beginning of month, end of month, something else? New and returning customers – DAX Patterns
Hi,
I didn't include the dates table because it was rather big, you can re-create it with this expression (couldn't format it as code, the browser kept telling me there was invalid HTML in the body, sorry):
FullCalendar = CALENDAR(FIRSTDATE(InvoiceMetric[Date]), LASTDATE(InvoiceMetric[Date]))
What I'm trying to achieve is a bar-graph with a bar per month per module, showing the total number of active licenses for that month (so for all customers combined). The months and years to show would be selected using a slicer. Later on, I would like to make a waterfall chart for the last quarter with beginning condition (previous Q), +new in that Q, -lost in that Q, and total of this Q. All of this always for the total nr of licenses, never per customer.
Thanks for the suggested read, I'll work my way through it and see if it fits with my needs. By the title of it, I would guess it's indeed what I'm looking for. I wasn't aware that there was such a thing as 'DAX patterns', haven't come across it yet on the forum nor any of my google searches...thanks for the tip!
