Forum Discussion

BM_'s avatar
BM_
Frequent Visitor
4 years ago
Solved

Earlier function in M code

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.

 

MEMBERIDSubscriptionEndDateLastSubscriptionDate
110-01-201910-01-2020
110-01-202010-01-2020
217-03-201417-03-2014
301-04-201418-12-2022
318-12-202218-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!
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

10 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
      NilR
      Post Patron
      2 years ago

      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
    )

       

      • smpa01's avatar
        smpa01
        Community Champion
        2 years ago

        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 

  • BM_'s avatar
    BM_
    Frequent Visitor
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.