Forum Discussion
Sales total For Specific period
- 1 year ago
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).
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).
Do you know how I could adopt this formula to do a | sales year to date | sales prior year to date | ?