my input :
Item Price FromDate ToDate
A 10 2023-11-01 2023-11-30
A 15 2023-12-01 2023-12-31
A 12 2024-01-01 2024-01-31
A 14 2024-02-01 9999-12-31 (Current price as of today)
Expected output: (If i run it today)
Item Price FromDate ToDate
A 10 2023-11-01 2023-11-30
A 15 2023-12-01 2023-12-31
A 12 2024-01-01 2024-01-31
A 14 2024-02-01 2024-02-29
A 14 2024-03-01 2024-03-31
A 14 2024-04-01 2024-04-30
A 14 2024-05-01 2024-05-31
I used generate rows tool but getting duplicates, please help me
Thanks Alexnajm,
Please see my updated requirement below since it has changed and kindly help me in this regard.
my input :
Item Price FromDate ToDate
A 10 2023-08-01 2023-08-31
A 12 2023-09-01 2023-10-31
A 14 2023-11-01 2023-12-31
A 15 2024-01-01 2024-01-31
A 20 2024-02-29 9999-12-31
Expected output:
Item Price FromDate
A 10 2023-08-01
A 12 2023-09-01
A 12 2023-10-01
A 14 2023-11-01
A 14 2023-12-01
A 15 2024-01-01
A 20 2024-02-01
and if it is possible we need 'ToDate' as well, like below:
Item Price FromDate ToDate
A 10 2023-08-01 2023-08-31
A 12 2023-09-01 2023-09-30
A 12 2023-10-01 2023-10-31
A 14 2023-11-01 2023-11-30
A 14 2023-12-01 2023-12-31
A 15 2024-01-01 2024-01-31
A 20 2024-02-01 2024-02-29
Many thanks in advance
@BalaBhaskar
We can use the DateTimeTrim function to get the FirstOfMonth and LastofMonth, which is convenient.
Thanks very much Qiu, it worked