Getting max values if date is between date column from another table

[awff]

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!

[awff · Accepted solution]

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)

)

RETURN

SWITCH(

    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]

)

[Fowmy]

@awff 

As per your description, Product A license got upgarded to 11 Licenses (1+10), not 16. 
According to the log, Product A has beeen used by three users (Andrew, Jane and Bob) between 1/1/2023 to 15/1/2023. Therefor excceded by 2. Is this what you need?

[awff]

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.

[awff]

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