Forum Discussion
Sales total For Specific period
Hi CQueen
This variable returns the last date within the current filter context, not the latest date across all visible or selected rows. So, if you use this in a visual along with the Billing Date column, the result will just mirror the Billing Date for each row.
VAR Max_Date = LASTDATE('CON V.Fact.invoice'[Billing Date])Try this:
52 Week Case QTY = VAR MaxDate = CALCULATE ( MAX ( 'CON V.Fact.invoice'[Billing Date] ), ALLSELECTED ( 'CON V.Fact.invoice' ) ) VAR StartDate = MaxDate - 364 RETURN CALCULATE ( SUM ( 'CON V.Fact.invoice'[Total Cases] ), KEEPFILTERS ( 'CON V.Fact.invoice'[Billing Date] >= StartDate ) )KEEPFILTERS is generally faster as it works with the existing filter context instead of creating a row context over the entire table (as FILTER does).
Super UserHi 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,
I reached out to Microsoft and it looks like it is a bug in the back end. Your feedback is correct and I appreciate your help.