topic how to filter in calculate sum in DAX Commands and Tips

how to filter in calculate sum

konradjonsson — Tue, 29 Jun 2021 13:09:05 GMT

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]

)

Re: how to filter in calculate sum

Anonymous — Tue, 29 Jun 2021 14:26:12 GMT

Even before you write a single formula, I'd strongly suggest that you get familiar with time-intelligence in PBI because I can tell you one thing right now: you're not doing it right. The automatic date tables in PBI should never be used if you want to create something useful for others or put it in a production environment. Instead, you should always create a proper Date table. There are just too many reasons to enlarge upon.

Standard time-related calculations – DAX Patterns

Re: how to filter in calculate sum

konradjonsson — Tue, 29 Jun 2021 14:58:14 GMT

Hi. Thanks for the feedback.

I am familiar with time-intelligence.

The formula written was automatically created when I chose 'New quick measure'.

-what is missing is the filter on external sales.