Forum Discussion
Getting max values if date is between date column from another table
Hi there!
I am creating a report from usage log files where I have tabulated the values.
I have created two tables, one is a Product Log/History table which shows what each customer has and the number of licenses in the period. The other is the usage log which is simple when they log in to use the product.
Basically, I need to show if the number of users has exceeded their license count for the product in each session (day).
For example...
This is a product table:
| Product | Version | CustomerName | StartDate | Licenses |
| A | 1 | Customer 1 | 1/01/2023 | 1 |
| B | 1 | Customer 1 | 1/01/2023 | 2 |
| C | 1 | Customer 1 | 1/01/2023 | 5 |
| B | 2 | Customer 1 | 16/05/2023 | 2 |
| A | 3 | Customer 1 | 16/05/2023 | 10 |
| B | 3 | Customer 1 | 4/09/2023 | 2 |
| C | 3 | Customer 1 | 4/09/2023 | 5 |
| D | 3 | Customer 1 | 4/09/2023 | 5 |
This is the Usage Log table
| SessionStart | CustomerName | Product | User |
| 1/02/2023 | Customer 1 | A | Andrew |
| 1/02/2023 | Customer 1 | A | John |
| 1/02/2023 | Customer 1 | B | Andrew |
| 1/02/2023 | Customer 1 | A | Bob |
| 1/02/2023 | Customer 1 | A | Bob |
| 1/02/2023 | Customer 1 | A | Jane |
| 1/02/2023 | Customer 1 | C | Jane |
| 1/02/2023 | Customer 1 | C | Jane |
| 1/02/2023 | Customer 1 | A | Andrew |
| 6/06/2023 | Customer 1 | C | Sam |
| 6/06/2023 | Customer 1 | C | Sam |
| 6/06/2023 | Customer 1 | C | Sam |
| 6/06/2023 | Customer 1 | C | Sam |
| 6/06/2023 | Customer 1 | A | Bob |
| 6/06/2023 | Customer 1 | A | Jane |
Just looking at Product A... [StartDate] is 1/01/2023 and has 1 license and the next update is 16/05/2023 when they upgraded to 16 licenses. So between 1/01/2023 to 15/05/2023 there is only 1 license available.
In the Usage Log, for the session 1/02/2023 is between 1/01/2023 to 15/05/2023 so they should only have one license. How can I create either a measure or a calculated column to show the max licenses from the Product Log?
Ideally I can use this as a constant line in a chart to show periods where the customer has exceeded their license count.
Thanks!
- Found a solution:
In the product table I needed to get the end date for each product in the same row with a calculated col:SessionNextUpdate =Var FromDate = 'ProductLog'[SessionStart]VAR Customer = 'ProductLog'[Customer]VAR Product = 'ProductLog'[Product]VAR res =CALCULATE(MIN('ProductLog'[SessionStart]),FILTER('ProductLog','ProductLog'[Product] = Product &&'ProductLog'[Customer] = Customer &&'ProductLog'[SessionStart] > FromDate))RETURNSWITCH(TRUE(),res = blank(), DATE(9999,12,31), res - 1)
Then in the Usage Log, another calculated column:MaxLicenses =maxx(FILTER('ProductLog','UsageLog'[Customer] = 'ProductLog'[Customer] &&'UsageLog'[Product] = 'ProductLog'[Product] &&'UsageLog'[SessionStart] >= 'ProductLog'[SessionStart] &&'UsageLog'[SessionStart] <= 'ProductLog'[SessionNextUpdate]),'ProductLog'[Licenses])
4 Replies
I'm not sure how to attach a pbix file as a sample here, but here is a sample dataset:
Usage Log:
SessionStart CustomerName Product User 1/02/2023 Customer 1 A Andrew 1/02/2023 Customer 1 A John 1/02/2023 Customer 1 B Andrew 1/02/2023 Customer 1 A Bob 1/02/2023 Customer 1 A Bob 1/02/2023 Customer 1 A Jane 1/02/2023 Customer 1 C Jane 1/02/2023 Customer 1 C Jane 1/02/2023 Customer 1 A Andrew 6/06/2023 Customer 1 C Sam 6/06/2023 Customer 1 C Sam 6/06/2023 Customer 1 C Sam 6/06/2023 Customer 1 C Sam 6/06/2023 Customer 1 A Bob 6/06/2023 Customer 1 A Jane 6/06/2023 Customer 1 A Sam 6/06/2023 Customer 1 A Bob 6/06/2023 Customer 1 B Bob 23/05/2023 Customer 2 D Tim 23/05/2023 Customer 2 D Tim 23/05/2023 Customer 2 D Steven 23/05/2023 Customer 2 A James 23/05/2023 Customer 2 C James 23/05/2023 Customer 2 C James 1/01/2023 Customer 3 A Jack 1/01/2023 Customer 3 B Jack 31/05/2023 Customer 3 A Jack 7/12/2023 Customer 3 B Jack Product Log:
Product Version CustomerName StartDate Licenses A 1 Customer 1 1/01/2023 1 B 1 Customer 1 1/01/2023 2 C 1 Customer 1 1/01/2023 5 B 2 Customer 1 16/05/2023 2 A 3 Customer 1 16/05/2023 10 B 3 Customer 1 4/09/2023 2 C 3 Customer 1 4/09/2023 5 D 3 Customer 1 4/09/2023 5 B 1 Customer 2 9/07/2023 2 C 1 Customer 2 9/07/2023 6 A 2 Customer 2 30/11/2023 5 B 3 Customer 2 30/11/2023 5 C 3 Customer 2 30/11/2023 5 D 3 Customer 2 30/11/2023 5 A 1 Customer 3 1/03/2023 1 A 2 Customer 3 20/05/2023 10 A 2 Customer 3 1/12/2023 15 B 3 Customer 3 5/12/2023 3 Hi Fowmy, hope you had a good weekend. The product data only provides the current total licenses for each new period. In this case:
from 1/01/2023 to 15/05/2023 the customer had only 1 license.
from 16/05/2023 onwards (or next update) the customer has a total of 10 licenses.
Your second statement about license usage is correct. Even if I can get a link to the two tables to show the max licenses held if the Usage SessionStart date falls between the product log dates.
- Found a solution:
In the product table I needed to get the end date for each product in the same row with a calculated col:SessionNextUpdate =Var FromDate = 'ProductLog'[SessionStart]VAR Customer = 'ProductLog'[Customer]VAR Product = 'ProductLog'[Product]VAR res =CALCULATE(MIN('ProductLog'[SessionStart]),FILTER('ProductLog','ProductLog'[Product] = Product &&'ProductLog'[Customer] = Customer &&'ProductLog'[SessionStart] > FromDate))RETURNSWITCH(TRUE(),res = blank(), DATE(9999,12,31), res - 1)
Then in the Usage Log, another calculated column:MaxLicenses =maxx(FILTER('ProductLog','UsageLog'[Customer] = 'ProductLog'[Customer] &&'UsageLog'[Product] = 'ProductLog'[Product] &&'UsageLog'[SessionStart] >= 'ProductLog'[SessionStart] &&'UsageLog'[SessionStart] <= 'ProductLog'[SessionNextUpdate]),'ProductLog'[Licenses])
