Date questions controle end date

[Endurion]

Hi everyone, I have a problem. I have a table with an ID, Date en need to get a colum End.

End must be the value of the next Date with the same ID. If there is no next the the end date must be the same as the date.

I struggel with this a lot, does someone have the awnser?

The picture below gives a simple overview. The yellow colum is what I am trying to make

[Nathaniel_C]

Hi @Endurion ,
If I understand you correctly, please try this

My original table is on the left, the solution is the table on the right

End = 

Var _id = MAX(NextDate[ID])
Var _date = MAX(NextDate[Date])

var _calc = CALCULATE(MIN(NextDate[Date]),FILTER(ALL(NextDate),NextDate[ID]=_id && NextDate[Date]>_date))

return if(ISBLANK(_calc),_date,_calc)

My table name is NextDate

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[ronrsnfld · Accepted solution]

In Power Query, try the following code

•  Group by ID
• Add a column to each subgroup consisting of the date column altered by
  • Removing the first entry
  • Duplicating the last entry

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}),

//Group by ID
//Then shift the date column up one (delete first entry,
//  adding the "last" date to the bottom
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"End", each
            Table.FromColumns(
                Table.ToColumns(_) &
                {List.RemoveFirstN([Date],1) & {List.Last([Date])}},
                {"ID","Date","End"}),
                type table[ID=Int64.Type,Date=date, End=date]}
    }),
    #"Expanded End" = Table.ExpandTableColumn(#"Grouped Rows", "End", {"Date", "End"})
        
        
in
    #"Expanded End"

Results from your Data above

[Endurion]

Thats that works, very nice and again thanks. Just one detailed question. I am not very familiar with Power Query and the Advanced editor. The dataset that I shared was an simplied version. What do I need to do if I want to add addtional columns that are in the base dataset (about 20 colums)?

Hope you can awnser this as wel 😄 

[ronrsnfld]

You may need to specify all the columns in the functions where only a few are specified now. Your own code should have generated a #"Changed Type" step that labels them there. You will have to add more column definitions in the Table.FromColumns function. There are methods to automate that but it depends on your column types, or you can just add them manually if they will be a constant.

[Endurion]

Hi @Nathaniel_C thanks for the quick response. This is a calculated colum I can see based on the DAX writing style. I need this to be done within the query editor due to other calculations here that need this value. Do you have knowlages how to do this within the query editor?

[Nathaniel_C]

Hi @Endurion ,
This is actually a Dax measure, not a calculated column.  
Sorry that I did not do it in Power Query, sometimes I forget which forum I am looking at.
The people that are my go to people for Power Query include @KenPuls and @ImkeF . Perhaps one of them can answer this.

Thank you,

Nathaniel