cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
konradjonsson
Helper II
Helper II

how to filter in calculate sum

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]
                )
            )
        )
)
2 REPLIES 2
Anonymous
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

Announcements
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