Forum Discussion

nilesh_amrutkar's avatar
nilesh_amrutkar
Helper I
6 years ago

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 an...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://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

 

 

nilesh_amrutkar's avatar
nilesh_amrutkar
Helper I
6 years ago

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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      nilesh_amrutkar
      Helper I
      6 years ago

      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,