cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nilesh_amrutkar
Frequent Visitor

calculate value based on different row

 

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.ItemStageDateCheck
A123

Jar

P01/01/20201
A123JarF05/01/20201
A123JarA10/01/20201
A123telP01/01/20200
A123telF05/01/20200
A123telA 0
B123jetP01/01/20201
B123jetF05/01/20201
B123jetA10/01/20201
B123jarP01/01/20200
B123jarF05/01/20200
B123jarA 0

 

Thanks,

Nilesh

4 REPLIES 4
amitchandak
Super User
Super User

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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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.ItemStage1.Date2.Date3.Date4.Date5.Date6.Date7.Date8.Date9.Date10.Date11.Date
A123JarP01-01-202008-01-202015-01-202022-01-202029-01-202005-02-202012-02-202019-02-202026-02-202004-03-202011-03-2020
A123JarF05-01-2020          
A123JarA10-01-202011-01-202012-01-202013-01-202014-01-202014-01-202015-01-2020    
A123telP01-01-202008-01-202002-01-202009-01-202003-01-202010-01-202004-01-202011-01-202005-01-202012-01-202006-01-2020
A123telF05-01-2020          
A123telA           
B123jetP05-01-202012-01-202019-01-202026-01-202002-02-202009-02-202016-02-202023-02-202001-03-202008-03-202015-03-2020
B123jetF05-01-202013-01-202015-01-202017-01-202020-01-202030-01-2020     
B123jetA10-01-202013-01-202014-01-202015-01-2020       
B123jarP03-01-202010-01-202017-01-202024-01-202031-01-202007-02-202014-02-202021-02-202028-02-202006-03-202013-03-2020
B123jarF05-01-2020          
B123jarA           

 

Thanks and Regards,

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors