cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## how to calculate with specific filter

Hello Team,

I tried to get the previous row value of column "P" based on column "Year" with below DAX to add NEW column "Previous_P",

 Statistic Group ID Year P 112042 2021 0 112042 2022 0 112042 2023 0 112042 2024 164.74 112042 2025 159.8 112042 2026 155.01 112042 2027 150.36 112042 2028 145.85 137165 2021 0 137165 2022 0 137165 2023 0 137165 2024 0 137165 2025 8525.5 137165 2026 8270.5 137165 2027 8024 137165 2028 7862.5

``Previous_P = SUMX(FILTER(Sheet1,Sheet1[Year]=EARLIER(Sheet1[Year])-1),Sheet1[P])``

after applying above DAX, I could get the below result:

but what I want is to get "previous_P" with group of different value of "Statistic Group ID".

I tried to use DAX as below, but could not get the expected data:

``````Previous_P =
CALCULATE(
SUMX('Sheet1',Sheet1[P]),
FILTER(Sheet1,Sheet1[Year]=(Sheet1[Year])-1),ALLEXCEPT(Sheet1,Sheet1[Statistic Group ID])
)``````

Could you support to propose how I can get the expected data?

Thank you!

1 ACCEPTED SOLUTION
Super User

@alson2002 , Try like

``Previous_P = SUMX(FILTER(Sheet1,Sheet1[Year]=EARLIER(Sheet1[Year])-1 && Sheet1[Statistic Group ID] =  earlier(Sheet1[Statistic Group ID] ) ),Sheet1[P])``

2 REPLIES 2
Super User

@alson2002 , Try like

``Previous_P = SUMX(FILTER(Sheet1,Sheet1[Year]=EARLIER(Sheet1[Year])-1 && Sheet1[Statistic Group ID] =  earlier(Sheet1[Statistic Group ID] ) ),Sheet1[P])``

New Member

Great, thank you!!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors