how to filter in calculate sum

[konradjonsson]

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]

                )

            )

        )

)

[Anonymous]

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

[konradjonsson]

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.