Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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.
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
@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.
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |