Forum Discussion
Solved
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 t...
- 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])
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 |