cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!!!