Earlier function in M code

[BM_]

Hi,

I have a table with memberships, I want to add a column that always gives the last enddate of a subscription for a specific member.

MEMBERID	SubscriptionEndDate	LastSubscriptionDate
1	10-01-2019	10-01-2020
1	10-01-2020	10-01-2020
2	17-03-2014	17-03-2014
3	01-04-2014	18-12-2022
3	18-12-2022	18-12-2022

I use the following DAX

LastSubscriptionDate = CALCULATE(MAX('MEMBER'[SubscriptionEndDate]),FILTER('MEMBER','MEMBER'[MEMBERID]=EARLIER('MEMBER'[MEMBERID])))

How do I do this in M code?

Thanks!

[smpa01 · Accepted solution]

@BM_  the equivalent M is following

let
  src=Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Vcu7CQAxEAPRXhQbtJIX7lyL2f7b8Cdz+piZE0KDgqJDA9UecVzxlk59p8krfUtS+YpM/WczqhY=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [MEMBERID = _t, SubscriptionEndDate = _t]
  ),
  CT = Table.TransformColumnTypes(
    src,
    {{"MEMBERID", Int64.Type}, {"SubscriptionEndDate", type date}}
  ),
  AC = Table.AddColumn(
    CT,
    "LastSubscriptionDate",
    each List.Max(Table.SelectRows(CT, (q) => q[MEMBERID] = [MEMBERID])[SubscriptionEndDate])
  )
in
  AC

[NilR]

I really Liked your approach!

I want to rewrite my below DAX Calculated column in M Query but was not sure how to write the Earlier in this funtion, can you help?

RANKX ( 
        FILTER (ALL('0410 tbl'),
            '0410 tbl'[GP] = EARLIER('0410 tbl'[GP])
            && '0410 tbl'[ID] = EARLIER('0410 tbl'[ID])
            && '0410 tbl'[DATEDIFF] = EARLIER('0410 tbl'[DATEDIFF])
            && '0410 tbl'[Start_Date] >= _start_date
            && '0410 tbl'[Start_Date] <= _end_date
        ),        
        '0410 tbl'[Start_Date],
        ,
        ASC,
        Dense
    )

[ImkeF]

Hi @NilR ,
can you please provide some sample data with the input and desired output data please?

[NilR]

I gave up and then added an statement to the sql and created something similar in my sql. Thank you!

DENSE_RANK() OVER (PARTITION BY.....)

[smpa01]

@NilR  my M is not as good as my SQL and /DAX. I can provide you a M but might not be optimized and scalable. Hence, looping

++ @AlexisOlson @ImkeF 

[AlexisOlson]

@NilR For this RANKX example, you can do something similar in Power Query by filtering Start_Date, grouping on the GP, ID, and DATEDIFF columns, sorting the result by Start_Date, and then adding an index column.

[Anonymous]

Hi @BM_ 

You could use Table.SelectRows() function to keep the "true" data. Then use Table.Group() to get the result.

In Power Query Editor=> Home , select Group By , then group by Column [MEMBERID], then add two aggregations . You can get a result like the screenshot below.

Then expand the column [all] , you will get a table like this :

Go back to Desktop view, add the columns [MEMBERID] [all.SubscriptionEndDate] [last] to a table visual .

I have attached my pbix file , you can refer to it.

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[visheshjain]

Hi @BM_ ,

Please have a look at the following video and I think it should be pretty much what you are looking for.

https://youtu.be/hidJ5T_DYQ0

Hope this helps.

Thank you,

Vishesh Jain

[BM_]

Hi @amitchandak thanks for sharing. My file has over 250k records, so this method is going to cost in performance. See the comments on the blog. Also I don't know how I would include the MAX function in this way.

[amitchandak]

@BM_ , You do not have the alternative of EARLIER there. create one more copy of the table and merge 

refer, if needed

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/