cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Changing the minimum of column value based on another column

Hi 

 

The below table has 3 start dates for two sites (last column).

 

Kolumam_0-1594112970077.png

 

Expected Output: As you can see from the below table, for Kanchan the first date of a site alone is changed to the start of the year (i.e from 1 April 2017 to 1st Jan 2017) and similarly for FDC Aurangabad, the first date is changed to 1/1/2019. The rest of the dates should be as it is. I have 100 sites and I want to do this for all. How should I proceed? Please help.

 

Start Date of ContractEnd Date of ContractComprehensive O&M PriceName
1/1/2017 31/3/2018 0:00380Kanchan India Pvt. Ltd.
1/4/2018 31/3/2020 0:00410Kanchan India Pvt. Ltd.
1/1/2019 31/8/2019 0:00375FDC Aurangabad
1/9/2019 31/3/2020 0:00405FDC Aurangabad
11/5/2020 31/12/2021 0:00303Kanchan India Pvt. Ltd.
12/5/2020 31/12/2021 0:00385FDC Aurangabad

 

@Amit @amitchandak @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Changing the minimum of column value based on another column

Hi @Kolumam ,

 

You need to add a new column  with the following code:

 

Date of contract =
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = 'Table'[Start Date of Contract];
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    'Table'[End Date of Contract]
)

 

If you want this to be dinamyc a measure you need to do the following syntax:

 

Measure Date of contract = 
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = SELECTEDVALUE('Table'[Start Date of Contract]);
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    SELECTEDVALUE('Table'[End Date of Contract])
)

 

Check PBIX file with both options.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: Changing the minimum of column value based on another column

Thank you @MFelix. for the clear answer. 

 

There is a mistake in the formula though: 

Correct Formula:

Date of contract =
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = 'Table'[Start Date of Contract];
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    'Table'[Start Date of Contract]
)

 

View solution in original post

6 REPLIES 6
Highlighted
Helper II
Helper II

Re: Changing the minimum of column value based on another column

Hi, 

  • using "SUMMARIZE", create a second table by "name"
  • calculate column min "start date of contract" for each name
  • calculate column start of year for "min of start date of contract"
  • relate this new table to your main table use the column "start of year" instead of "start date"

 

This is the solution creating a separate table. It helps better understanding the logic by creating a visible table. You can accomplish the same result by internally calculating the result but with an invisible, dynamically created table. I prefer this visible solution for beginners.

Alexander

Highlighted
Impactful Individual
Impactful Individual

Re: Changing the minimum of column value based on another column

Where do you want this change to occur?  During ETL in Power Query (before loading) or in your model using DAX (after loading the table)?

Highlighted
Post Prodigy
Post Prodigy

Re: Changing the minimum of column value based on another column

Hi @tjd 

 

I want this to be done using DAX (after loading the table). Let me know if it is possible.

Highlighted
Super User V
Super User V

Re: Changing the minimum of column value based on another column

Hi @Kolumam ,

 

You need to add a new column  with the following code:

 

Date of contract =
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = 'Table'[Start Date of Contract];
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    'Table'[End Date of Contract]
)

 

If you want this to be dinamyc a measure you need to do the following syntax:

 

Measure Date of contract = 
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = SELECTEDVALUE('Table'[Start Date of Contract]);
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    SELECTEDVALUE('Table'[End Date of Contract])
)

 

Check PBIX file with both options.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: Changing the minimum of column value based on another column

Thank you @MFelix. for the clear answer. 

 

There is a mistake in the formula though: 

Correct Formula:

Date of contract =
IF (
    CALCULATE (
        MIN ( 'Table'[Start Date of Contract] );
        ALLEXCEPT ( 'Table'; 'Table'[Name] )
    ) = 'Table'[Start Date of Contract];
    STARTOFYEAR ( 'Table'[Start Date of Contract] );
    'Table'[Start Date of Contract]
)

 

View solution in original post

Highlighted
Super User V
Super User V

Re: Changing the minimum of column value based on another column

@Kolumam ,

 

You are correct error typing the name of the colum picked up the first one with date in it 😄

 

Great catch.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors