Sales total For Specific period

Hi CQueen ,

The issue in your original measure stems from how the Weeks_Ago_52 variable is calculated. Using Max_Date - 364 directly works as a scalar subtraction but isn't robust when used inside a filter context. Also, wrapping Max_Date - 364 inside CALCULATE is not valid since Max_Date is already a scalar. To fix this, you should clearly define both the start and end dates for your 52-week range and use them in a FILTER expression or DATESBETWEEN if a proper date table is available. Here's the revised version using the invoice table directly:

52 Week Case QTY =
VAR Max_Date = MAX('CON V.Fact.invoice'[Billing Date])
VAR Min_Date = Max_Date - 364
RETURN
    CALCULATE(
        SUM('CON V.Fact.invoice'[Total Cases]),
        FILTER(
            'CON V.Fact.invoice',
            'CON V.Fact.invoice'[Billing Date] >= Min_Date &&
            'CON V.Fact.invoice'[Billing Date] <= Max_Date
        )
    )

If you're using a separate Date table and it is marked correctly and related to the invoice table, it's better to use DATESBETWEEN:

52 Week Case QTY =
VAR Max_Date = MAX('CON V.Fact.invoice'[Billing Date])
VAR Min_Date = Max_Date - 364
RETURN
    CALCULATE(
        SUM('CON V.Fact.invoice'[Total Cases]),
        DATESBETWEEN('Date'[Date], Min_Date, Max_Date)
    )

This ensures only data from the last 52 weeks ending on the latest billing date is included, excluding anything from 2023 or earlier.

Best regards,