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

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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:

Best Regards,

Lin

[Anonymous]

@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. 

[v-lili6-msft]

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

[Anonymous]

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

@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.  

[Anonymous]

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:

Best Regards,

Lin

---