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

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

Reply
alson2002
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 IDYearP
11204220210
11204220220
11204220230
1120422024164.74
1120422025159.8
1120422026155.01
1120422027150.36
1120422028145.85
13716520210
13716520220
13716520230
13716520240
13716520258525.5
13716520268270.5
13716520278024
13716520287862.5

 

 

 

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

 

 

 

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

2021-10-18_14h15_17.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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

alson2002_0-1634538316459.png

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
amitchandak
Super User
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])

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
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])

 

Great, thank you!!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.