Adding the last date as a new column within the latest added row in M

[BarisT]

Dear Power BI users,

I would like to add the Last Enddate from ID 14451 as a new column into ID 14613. This is going about the contract of an employee. The first inflow was at 9 march 2022 and outflow was 25 may 2022. Later the same employee with EmployeeID 22671 flowed in at 3 june 2022. The latest inflow with startdate friday, 3 june 2022 and ID 14631 should always take the Last Enddate as a new row from the previous ID 14451. Below in the picture you can see my desired output. And the power BI file for this is via the following link:  https://1drv.ms/u/s!Al86B9l3mXfCcDTX7RtpkYNR7pk?e=T4QvHO

How can I add this Last Enddate column in the query editor or DAX?

Thank you in dvance for the help!

[v-yalanwu-msft]

Hi, @BarisT ;

You could change the custom column :

= Table.AddColumn(#"Expanded a", "Custom", each if [Enddate1] = null  then [enddatemax] else if  Duration.Days([Enddate1]-[Startdate1])<=60 then 
let  a = [EployeeID] , 
Enddate1= Table.SelectRows(#"Expanded a",each [EployeeID]  = a)[Enddate1] in
List.Max(List.Select(Enddate1, (x)=>x<_[Enddate1])) else null)

M language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldCxDoMgEADQX7kwkwgHqHRz6dDEdOjQwThYJW0XTVAG/76IxtrUDk1IuDu4d4SiIFzGXBBKEOOE+/1on001Uji51oCggAzRl6cbOmJptOQ+0CEGjgfG/IIsJyWdQKn4BryapjV9MPPK1g/QK/pxNAKq7bgZUyrkKCSfsEs1OPt+n1wb8q4N5czdXT9AuiMh35eWFkx+Yai/NORSqK3m5o5zPXQ3YwH//bjyBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EployeeID = _t, Startdate = _t, Enddate = _t, Custom = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EployeeID", Int64.Type}, {"Startdate", type date}, {"Enddate", type date}, {"Custom", type date}, {"Column", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EployeeID"}, {{"enddatemax", each List.Max([Enddate]), type nullable date}, {"a", each _, type table [ID=nullable number, EployeeID=nullable number, Startdate=nullable date, Enddate=nullable date, Custom=nullable date, Column=nullable text]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"ID", "Startdate", "Enddate"}, {"ID1", "Startdate1", "Enddate1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded a", "Custom", each if [Enddate1] = null  then [enddatemax] else if  Duration.Days([Enddate1]-[Startdate1])<=60 then 
let  a = [EployeeID] , 
Enddate1= Table.SelectRows(#"Expanded a",each [EployeeID]  = a)[Enddate1] in
List.Max(List.Select(Enddate1, (x)=>x<_[Enddate1])) else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"enddatemax"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ID1", "ID"}, {"Startdate1", "Startdate"}, {"Enddate1", "Enddate"}})
in
    #"Renamed Columns"

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarisT]

I just did a pre-script:

select *
from xx

where date >= ' 01-01-2022'

to extract less rows than usual. This worked. Thank you for your help.

[BarisT]

Dear @v-yalanwu-msft 

This M query works very well. But, it does not load data when the rows are over 7500~. Thus, when I apply this for real live data after applying it is still loading and not working. There is some issues happening regarding this code. Is it because the code is very complex? It might help to exclude difference of 60 days? Hence, i could always add an additional column considering 60 days. Thank you in advance.

 Yours sincerely,

Baris

[v-yalanwu-msft]

Hi, @BarisT ;

First you could group by the coulmn .

Then add create a condition column.

M language is 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldCxCoMwEADQXzkyB0wuiTbdXDoUpEMLHcTBaqhdFKIZ/PtGLTZSl0Igd7ncu5A8J1zGXBBKEOOE+/1kX3U5Uji71oCggAzRH083dMQO0Sf3gZ5j4HhkzC9IM1LQCZSKB+Dd1K3pZzMrbdWAXtFNaQRU4bgFU2rOUUg+YddycPb7Prk23BpnAynekZDvS6l7un4ATNaerGs3Ff2jIZdChZpbOi7V0D2MBfz344o3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EployeeID = _t, Startdate = _t, Enddate = _t, Custom = _t, Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EployeeID", Int64.Type}, {"Startdate", type date}, {"Enddate", type date}, {"Custom", type date}, {"Column", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EployeeID"}, {{"enddatemax", each List.Max([Enddate]), type nullable date}, {"a", each _, type table [ID=nullable number, EployeeID=nullable number, Startdate=nullable date, Enddate=nullable date, Custom=nullable date, Column=nullable text]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"ID", "Startdate", "Enddate"}, {"ID1", "Startdate1", "Enddate1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded a", "Custom", each if [Enddate1] = null then [enddatemax] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"enddatemax"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"ID1", "ID"}, {"Startdate1", "Startdate"}, {"Enddate1", "Enddate"}})
in
    #"Renamed Columns"

Then final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarisT]

Dear @v-yalanwu-msft the ID 15212 is missing the last enddate, namely 26 may 2022. What about that? It should always take the previous enddate? Thank you in advance. I hope we can solve it, because this issue happened to more power bi users in my environment which is still not solved.

[v-yalanwu-msft]

Hi, @BarisT ;

ID 15212 is missing the last enddate, namely 26 may 2022. why ? can you tell me your logic?  ID 15212 has an end date, why add it? Can you give a few more examples and then share what you want to output?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarisT]

Hello @v-yalanwu-msft , because in the end i would like to give the difference between startdate of an employee respectively to the last enddate of the same employee. If the difference is more than 60 days than it should not be counted as an inflow. Because some employees are flowing in in such a short time that it is not a good indication of an inflow and outflow. Thus, in the end it is important to know the previous enddate to calculate the difference between startdate and last enddate to get the number of days past. 

[v-yalanwu-msft]

Hi, @BarisT ;

In power quey , you could add a column:

=if [Enddate]=null then List.Max(List.Select(#"Changed Type"[Enddate],(x)=>x<>null)) else null

M language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxMzRW0lEyMjIzNwTSxrpmukYGRkZAplKsDkjexNQQSd5S1xgmb2Sqawphx8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EployeeID = _t, Startdate = _t, Enddate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Startdate", type date}, {"Enddate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Enddate]=null then List.Max(List.Select(#"Changed Type"[Enddate],(x)=>x<>null)) else null)
in
    #"Added Custom"

In dax:

Column = IF([Enddate]=BLANK(),MAX('Employee'[Enddate]),BLANK())

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BarisT · Accepted solution]

When you have multiple rows the M query code randomly assigns the same enddate to all employee. Please see my power bi file. I hope we could fix this bug or issue. Thank you in advance and the link for the power BI file which i have tried is below. https://1drv.ms/u/s!ApwLzcPKGlcdkAkemVy7h-FPuHgD?e=D93Djt

Thank you in advance.

[SpartaBI]

@BarisT write this:

Last Enddate = 
VAR _current_row_id = Employee[ID]
VAR _current_employee = Employee[EmployeeID]
RETURN
    MAXX(
        FILTER(
            Employee,
            Employee[EmployeeID] = _current_employee && Employee[ID] < _current_row_id
        ),
    Employee[Enddate]
    )

Showcase Report – Contoso By SpartaBI

      

[BarisT]

How do I apply this formula in M query editor instead of a calculated column?