Changing the minimum of column value based on another column

[Kolumam]

Hi 

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

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 Contract	End Date of Contract	Comprehensive O&M Price	Name
1/1/2017	31/3/2018 0:00	380	Kanchan India Pvt. Ltd.
1/4/2018	31/3/2020 0:00	410	Kanchan India Pvt. Ltd.
1/1/2019	31/8/2019 0:00	375	FDC Aurangabad
1/9/2019	31/3/2020 0:00	405	FDC Aurangabad
11/5/2020	31/12/2021 0:00	303	Kanchan India Pvt. Ltd.
12/5/2020	31/12/2021 0:00	385	FDC Aurangabad

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

[MFelix · Accepted solution]

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.

[tjd]

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)?

[Kolumam]

Hi @tjd 

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

[MFelix · Accepted solution]

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.

[Kolumam · Accepted solution]

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]
)

[MFelix]

@Kolumam ,

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

Great catch.

[Alexander76877]

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