Forum Discussion

awff's avatar
awff
Helper III
2 years ago
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...
Need Help
  • awff's avatar
    awff
    2 years ago
    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]
    )
awff's avatar
awff
Helper III
2 years ago
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]
)