Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

how to filter in calculate sum



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 =
    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])
            Append1[Invoice Date].[Date],
                SUMMARIZE(VALUES(Append1), Append1[Invoice Date].[Year]),
                    Append1[Invoice Date].[QuarterNo],
                    Append1[Invoice Date].[Quarter],
                    Append1[Invoice Date].[MonthNo],
                    Append1[Invoice Date].[Month],
                    Append1[Invoice Date].[Day]
Not applicable

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

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.

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors