calculate value based on different row

Question

&nbsp;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.&nbsp;Project No.ItemStageDateCheckA123JarP01/01/20201A123JarF05/01/20201A123JarA10/01/20201A123telP01/01/20200A123telF05/01/20200A123telA&nbsp;0B123jetP01/01/20201B123jetF05/01/20201B123jetA10/01/20201B123jarP01/01/20200B123jarF05/01/20200B123jarA&nbsp;0&nbsp;Thanks,Nilesh

amitchandak · Answer

Create a new column likeColumn = if(ISBLANK(minx(filter(Sheet1,Sheet1[Project No.]=EARLIER(Sheet1[Project No.]) &amp;&amp; Sheet1[Item]= EARLIER(Sheet1[Item]) &amp;&amp; (Sheet1[Stage])="A" &amp;&amp; Sheet1[Date]&gt;=EARLIER(Sheet1[Date])),Sheet1[Date])),0,1)&nbsp;Refer : https://www.dropbox.com/s/pgtdjaphy92xfhd/431687.pbix?dl=0&nbsp;&nbsp;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-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601&nbsp;&nbsp;

nilesh_amrutkar · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;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.&nbsp;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.&nbsp;Could you please help to formulate measure for both cases.&nbsp;Thanks and Regards,Nilesh Amrutkar

amitchandak · Answer

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 · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;See the table below:&nbsp;When stage is A and date is provided, then I want to highlight the dates in both P&amp;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.&nbsp;Please suggest what is better? and also if you can help with single measure for all columns that would be fantastic.&nbsp;Project No.ItemStage1.Date2.Date3.Date4.Date5.Date6.Date7.Date8.Date9.Date10.Date11.DateA123JarP01-01-202008-01-202015-01-202022-01-202029-01-202005-02-202012-02-202019-02-202026-02-202004-03-202011-03-2020A123JarF05-01-2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A123JarA10-01-202011-01-202012-01-202013-01-202014-01-202014-01-202015-01-2020&nbsp;&nbsp;&nbsp;&nbsp;A123telP01-01-202008-01-202002-01-202009-01-202003-01-202010-01-202004-01-202011-01-202005-01-202012-01-202006-01-2020A123telF05-01-2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A123telA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B123jetP05-01-202012-01-202019-01-202026-01-202002-02-202009-02-202016-02-202023-02-202001-03-202008-03-202015-03-2020B123jetF05-01-202013-01-202015-01-202017-01-202020-01-202030-01-2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B123jetA10-01-202013-01-202014-01-202015-01-2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B123jarP03-01-202010-01-202017-01-202024-01-202031-01-202007-02-202014-02-202021-02-202028-02-202006-03-202013-03-2020B123jarF05-01-2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B123jarA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks and Regards,

Forum Discussion

calculate value based on different row

4 Replies

Related content

Dynamic Calculation Different Period

DAX for calculating retroactive values

Date Different return wrong value

Different Calculations

Using CALCULATE to summarize different values

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