create a new column date of next tax and list from date to the new column date

[eliasayyy]

hello guys if title is confusing,
i have tax rate that changes not necessarly once a month or anything but it changes and i want to create a list of date from date to the next date so here is the table example:

tax_value	date
0.25	8/1/2022
0.3	10/1/2022
0.33	9/1/2022
0.25	11/1/2022
0.28	11/13/2022
0.33	12/1/2022

my desire result in mquery is to get the date of the next date in the period and the latest date is till today

so in this example i want to create a new column where the result will be :

tax_value	date	Expected Result
0.25	8/1/2022	9/1/2022
0.3	10/1/2022	11/1/2022
0.33	9/1/2022	10/1/2022
0.25	11/1/2022	11/13/2022
0.28	11/13/2022	12/1/2022
0.33	12/1/2022	date of today

is this possible and acheivable through power query?
@Anonymous you helped me with something close to this so any idea?

[eliasayyy]

@FreemanZ @tamerj1 thank you for your replies but i woud like to achieve it through power query

[Anonymous · Accepted solution]

Hello @eliasayyy , in order to achieve it through power query please follow the steps below.

First you have to sort column of date ascending if you want them in order now follow up:

1) Add a new index column 

2) Add a new column named test for example with the below formula:

Try [date] {[Index] + 1} otherwise null

3) add a new column named End Date with the below formula

If [test] is null then Datetime.LocalNow() else Date.AddDays([test],-1)

4) change type to date

Given you mentioned you want to create a list from start date to end date follow the rest:

5) add a new column with the formula

{Number.From([date])..Number.From([End Date])

6) on the created column, click on the column and press expand to new rows

7) change type to date 

8(optional)) delete old date, ,test,end date, index and you're left with tax value and the  new Date column

If i solved your problem, kindly give a thumbs up and mark as solved

[eliasayyy]

This works perfrctly thank you very much

[tamerj1]

Hi @eliasayyy 
Please refer to the attached sample file with the solution

Expected Result = 
VAR CurrentDate = 'Table'[Date]
VAR TableAfter =
    FILTER ( 'Table', 'Table'[Date] > CurrentDate )
VAR NextDate =
    COALESCE ( MINX ( TableAfter, 'Table'[Date] ), TODAY () )
RETURN
    NextDate

[FreemanZ]

hi @eliasayyy 

If you also consider DAX, this shall work:

Result =

MINX(

    FILTER(

        TableName,

        TableName[date]>EARLIER(TableName[date]),

    TableName[date]

)