cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create a date column from existing date column as one day minus the existing date column

I have a date column, called Eff_Date and a column name cmg as below,

Eff_Date          cmg
15-12-2017     F1
18-12-2018     F1
1-2-2019        F2
and I want to create a new Date Column which is one day previous to the date in the next row in  eff_date Column as below for a particular cmg
Eff_Date               NewDateColumn
15-12-2017          17-12-2018
18-12-2018           18-12-2018
1-2-2019                1-2-2019

 

the NewDateColumn will have (17-12-2018 , as next row in Eff_date has 18-12-2018) for cmg = F1
the Last row will be same as eff_date for a particular cmg
Please help

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create a date column from existing date column as one day minus the existing date column

HI, @Anonymous 

Just use EARLIER in this formula to create a new column

NewDateColumn = 
IF (
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    )
        = BLANK (),
    'Table'[Eff_Date],
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    ) - 1
)

Result:

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Create a date column from existing date column as one day minus the existing date column

HI, @Anonymous 

Just use EARLIER in this formula to create a new column

NewDateColumn = 
IF (
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    )
        = BLANK (),
    'Table'[Eff_Date],
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    ) - 1
)

Result:

2.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Create a date column from existing date column as one day minus the existing date column

Capture.PNG

Hey, 
Thank you so much. This is working perfectly for nonrepeating eff_date. 😄
However, this is not working where the eff_dates are repeating. 


@v-lili6-msft wrote:

HI, @Anonymous 

Just use EARLIER in this formula to create a new column

NewDateColumn = 
IF (
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    )
        = BLANK (),
    'Table'[Eff_Date],
    CALCULATE (
        MIN ( 'Table'[Eff_Date] ),
        FILTER (
            'Table',
            'Table'[cmg] = EARLIER ( 'Table'[cmg] )
                && 'Table'[Eff_Date] > EARLIER ( 'Table'[Eff_Date] )
        )
    ) - 1
)

Result:

2.JPG

 

Best Regards,

Lin




Anonymous
Not applicable

Re: Create a date column from existing date column as one day minus the existing date column

Capture.PNG

@v-lili6-msft ,
Thank you so much. 
This works perfectly for non-repeating eff_date. 😄
However, this isn't working for repeating eff_dates.
Please see the image attached. 
Thank you again. Really appreciate it. 

Community Support Team
Community Support Team

Re: Create a date column from existing date column as one day minus the existing date column

hi, @Anonymous 

What is your expected output in for repeating eff_dates?

Do you mean the top 3 row should show 01-01-2008 and then the for fourth row show 30-4-2008?

 

Best Regards,

LIN

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Create a date column from existing date column as one day minus the existing date column

@v-lili6-msft ,
The 5th row should show (4th-row'date -1) i.e a day previous to 4th row's date and for repeating columns it could stay the same.

Anonymous
Not applicable

Re: Create a date column from existing date column as one day minus the existing date column

@v-lili6-msft 
Let me explain you the business logic,
So for a particular cmg, 
the max of eff_date = current date 
and the dates in next row should be equal to the date in the previous row - 1.
so for 
1st row the eff_date = '15-12-2017' which is the maximum for cmg= 'LV1', so the newdatecolumn = current date.
for 2md row the eff_date = '01-08-2014', so the newdatecolumn = '15-12-2017 's previous day, i.e '30-11-2017'.
same logic goes for remaining dates. 
even for repeating dates. 
Basically, it is dependent on its previous rows.  
Capture.PNG

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors