Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Zakaria_1980
Post Patron
Post Patron

help

Hi,

please if i have such Tbl in PBI desctop and I want to add 2 other columns where it will put the first start date in all non blank rows per Full Name and in the other column the last end date in all non blank rows per Full Name (Output in the 2nd snapshot)

 

1st Snapshot:

 

FullNameMissionStart DateEnd Date
XM123/12/202226/02/2023
XM227/02/202304/03/2023
XM304/04/202312/05/2023
XM413/05/202330/06/2023
XM501/07/202331/07/2023
XM601/08/202330/08/2023
XM7  
YM101/01/202331/01/2023
YM201/02/202328/02/2023
YM301/03/202331/03/2023
YM4  
YM5  
YM6  
YM7  

 

2nd Snapshot:

 

FullNameMissionStart DateEnd DateAct. Start DateAct. End Date
XM123/12/202226/02/202323/12/202230/08/2023
XM227/02/202304/03/202323/12/202230/08/2023
XM304/04/202312/05/202323/12/202230/08/2023
XM413/05/202330/06/202323/12/202230/08/2023
XM501/07/202331/07/202323/12/202230/08/2023
XM601/08/202330/08/202323/12/202230/08/2023
XM7    
YM101/01/202331/01/202301/01/202331/03/2023
YM201/02/202328/02/202301/01/202331/03/2023
YM301/03/202331/03/202301/01/202331/03/2023
YM4    
YM5    
YM6    
YM7    
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

These calculated column formulas work

Actual start date = if(data[Start Date]=BLANK(),BLANK(),CALCULATE(MIN(data[Start Date]),FILTER(data,data[FullName]=EARLIER(data[FullName]))))
Actual end date = if(data[Start Date]=BLANK(),BLANK(),CALCULATE(max(data[End Date]),FILTER(data,data[FullName]=EARLIER(data[FullName]))))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Zakaria_1980
Post Patron
Post Patron

Hi,

please if i need to add another 2 Columns called "2023 Actual Start Date" and "2023 Actual End date", where:

he will check:

if Actual end date in 2023 or more (2024,2025..) and Actual start date in 2023 or more, keep the same data

if Actual End date in 2023 or more  and Actual start date less than 2023, replace that date with 01/01/2023 in 2023 Actual start date column and keep Actual end date as it is.

output for our previous example will be as follow:

Zakaria_1980_0-1680429983221.png

 

Hi,

Cannot understand your question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

These calculated column formulas work

Actual start date = if(data[Start Date]=BLANK(),BLANK(),CALCULATE(MIN(data[Start Date]),FILTER(data,data[FullName]=EARLIER(data[FullName]))))
Actual end date = if(data[Start Date]=BLANK(),BLANK(),CALCULATE(max(data[End Date]),FILTER(data,data[FullName]=EARLIER(data[FullName]))))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

Top Solution Authors