Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors