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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
vs531
New Member

Apply filter on CURRENTGROUP

Hi 

I am trying to calculate the difference between the last and previous observations for each ID. In the dataset, I have multiple observations for each ID over time. As a first step, I want to create a new table with the last and previous dates. I m using GROUPBY, but I cannot apply any filter to CURRENTGROUP. What would be a better approach to the problem?

 

Thanks 

 

 

NewTable =

GROUPBY(
    table,table[ID],
    "LastDate",MAXX(CURRENTGROUP(),table[DATE]),
    "PreviousDate", MAXX(FILTER(CURRENTGROUP(), table[DATE] < MAXX(CURRENTGROUP(),table[DATE]),table[DATE] )
    ))

 

vs531_0-1674497590994.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@vs531 , In case you need a new column

 

Diff =

var _max = maxx(filter(Table, [ID] = earlier([ID]) && [Date] < earlier([Date]) ) , [Date])
return
[Score]- Maxx(filter(Table, [ID] = earlier([ID]) && [Date] =_max ) , [Score])

 

View solution in original post

2 REPLIES 2
vs531
New Member

Thanks @amitchandak 

amitchandak
Super User
Super User

@vs531 , In case you need a new column

 

Diff =

var _max = maxx(filter(Table, [ID] = earlier([ID]) && [Date] < earlier([Date]) ) , [Date])
return
[Score]- Maxx(filter(Table, [ID] = earlier([ID]) && [Date] =_max ) , [Score])

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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