Hi.
I have created a formula that calculates a sum of the last five years sale of a product (quantities).
Now I need to include a filter expression, as I only want to include the transactions that are related to external sales.
The field I want to filter on is Append1[InternalCustomer]. The value I want to keep is '1' (can be 1 or 0).
How do I do this? Where in the formula do I add this expression?
Installed Base L5Y =
IF(
ISFILTERED(Append1[Invoice Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFYEAR(Append1[Invoice Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
Append1[Invoice Date].[Date],
STARTOFYEAR(DATEADD(__LAST_DATE, -5, YEAR)),
__LAST_DATE
)
RETURN
SUMX(
CALCULATETABLE(
SUMMARIZE(VALUES(Append1), Append1[Invoice Date].[Year]),
__DATE_PERIOD
),
CALCULATE(
SUM(Append1[OrderQuantity]),
ALL(
Append1[Invoice Date].[QuarterNo],
Append1[Invoice Date].[Quarter],
Append1[Invoice Date].[MonthNo],
Append1[Invoice Date].[Month],
Append1[Invoice Date].[Day]
)
)
)
)
