calculate value based on different row

[nilesh_amrutkar]

I have table with first four columns and need the 5th column "Check" as calculated column. based on following conditions.

1. Check if the project number is same, then check if the item is same and check if for stage "A", date is not blank then value in column check is 1 or else 0.

Project No.	Item	Stage	Date	Check
A123	Jar	P	01/01/2020	1
A123	Jar	F	05/01/2020	1
A123	Jar	A	10/01/2020	1
A123	tel	P	01/01/2020	0
A123	tel	F	05/01/2020	0
A123	tel	A		0
B123	jet	P	01/01/2020	1
B123	jet	F	05/01/2020	1
B123	jet	A	10/01/2020	1
B123	jar	P	01/01/2020	0
B123	jar	F	05/01/2020	0
B123	jar	A		0

Thanks,

Nilesh

[amitchandak]

Create a new column like

Column = if(ISBLANK(minx(filter(Sheet1,Sheet1[Project No.]=EARLIER(Sheet1[Project No.]) && Sheet1[Item]= EARLIER(Sheet1[Item]) && (Sheet1[Stage])="A" && Sheet1[Date]>=EARLIER(Sheet1[Date])),Sheet1[Date])),0,1)

Refer : https://www.dropbox.com/s/pgtdjaphy92xfhd/431687.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[nilesh_amrutkar]

Hi @amitchandak ,

Thanks a lot for the solution, I have 12 such date columns, and the value 1 or 0 which I am trying to get I want to use in conditional formatting.

What is the best way to create such multiple calculated column (1 each for 1 date column) or create 12 measures for each of column or can we create 1 measure for all these date columns.

Could you please help to formulate measure for both cases.

Thanks and Regards,

Nilesh Amrutkar

[amitchandak]

Can you share a sample and conditions you need. It can be one column or mutiple column. Depend on what is the later usages.

[nilesh_amrutkar]

Hi @amitchandak ,

See the table below:

When stage is A and date is provided, then I want to highlight the dates in both P&F stage with different color.

As per my original query first I thought I will create calculated column for each date column and based on that I would do conditional formatting based on output and even based on the solution you provided I could get the intended result, but with that my database is increasing and I am not sure what is the right way to have measure or to have calculated columns.

Please suggest what is better? and also if you can help with single measure for all columns that would be fantastic.

Project No.	Item	Stage	1.Date	2.Date	3.Date	4.Date	5.Date	6.Date	7.Date	8.Date	9.Date	10.Date	11.Date
A123	Jar	P	01-01-2020	08-01-2020	15-01-2020	22-01-2020	29-01-2020	05-02-2020	12-02-2020	19-02-2020	26-02-2020	04-03-2020	11-03-2020
A123	Jar	F	05-01-2020
A123	Jar	A	10-01-2020	11-01-2020	12-01-2020	13-01-2020	14-01-2020	14-01-2020	15-01-2020
A123	tel	P	01-01-2020	08-01-2020	02-01-2020	09-01-2020	03-01-2020	10-01-2020	04-01-2020	11-01-2020	05-01-2020	12-01-2020	06-01-2020
A123	tel	F	05-01-2020
A123	tel	A
B123	jet	P	05-01-2020	12-01-2020	19-01-2020	26-01-2020	02-02-2020	09-02-2020	16-02-2020	23-02-2020	01-03-2020	08-03-2020	15-03-2020
B123	jet	F	05-01-2020	13-01-2020	15-01-2020	17-01-2020	20-01-2020	30-01-2020
B123	jet	A	10-01-2020	13-01-2020	14-01-2020	15-01-2020
B123	jar	P	03-01-2020	10-01-2020	17-01-2020	24-01-2020	31-01-2020	07-02-2020	14-02-2020	21-02-2020	28-02-2020	06-03-2020	13-03-2020
B123	jar	F	05-01-2020
B123	jar	A

Thanks and Regards,