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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
itsAftab
Frequent Visitor

How to find last value entered each year

I have a table like this

StatusCustomerCustomer IDYearDate         
OKWWWW-1202201.02.2022 12:45
PendingWWWW-1202301.02.2023 14:45
PendingWWWW-1202301.08.2023 14:45
N/AYYYY-3202315.12.2023 01:45
OKAAAA-12024

01.01.2024 12:25

OKAAAA-12024

05.01.2024 13:25

 

I want to either create a measure or a flag-colum that:

- Finds the last status that was entered in the table the following year

 

The table should look like this:

StatusCustomerCustomer IDYearDate         
PendingWWWW-1202201.02.2022 12:45
PendingWWWW-1202301.08.2023 14:45
N/AYYYY-3202315.12.2023 01:45
OKAAAA-12024

05.01.2024 13:25

 

in 2022, customer WW was chnaged only once

in 2023, customer WW was changed twice but last time was 01.08.2023

in 2023 YY was chnged once

in 2024, AA was chnaged twice but last time was 05.01.2024

2 REPLIES 2
itsAftab
Frequent Visitor

Dear @Idrissshatila 
Thank you.
It sort of gave me what i wanted. 
What i forgot to mention was that there are other fields that will or will not change value(s) between each new line:

StatusCustomer-IDMembership status CommentsStatusYearChangeDate           
OKWW0Considering doubling 1202201-01-2022 00:01
OKWW-10x1202301-01-2023 11:00 03:00
PendingWW-21x999202201-01-2022 00:00
PendingWW-10x999202302-01-2023 03:00
N/AWW-23x0202401-01-2023 4:00
OKWW0Considering trippling1202301-01-2023 12:00
OKWWxConsidering ending1202302-01-2023 12:00

 

In the table above you can see that for the customers, sometimes the values remain the same but ChangeDate varies.
If i only apply Latest to date, i still get more than one record because other values are same.
I have to apply LATEST on all the columns


 

Idrissshatila
Super User
Super User

Hello @itsAftab 

 

you add all the fields in a table and set the date to the latest as in the screenshot.

Idrissshatila_0-1706287160276.png

 

and here is the pbix file attached for your reference.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors