Forum Discussion

walker4545's avatar
walker4545
Frequent Visitor
3 years ago
Solved

Number Recurring Value by Date

Hello Power BI gurus, 

 

I need to write a DAX statement that can order what number a customer's purchase was according to when the purchase was made:

 

Order Table

Customer IDProduct IDDate
C41PR48815/01/2015
C03PR10024/10/2018
C41PR1002/07/2015
C41PR1001/09/2014
C86PR15618/2/2020
C03PR10025/10/2018
C17PR4882/2/2019
C03PR48823/11/2018

 

Note there is no Primary Key in this table. 

 

In other words, I need another column that says what number the purchase was for that particular customer according to the date of the purchase. 

 

How could this problem be solved using a calculated column in DAX?

 

Thanks in advance.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Expected result CC =
RANKX (
    FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
    Data[Date],
    ,
    ASC
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Expected result CC =
RANKX (
    FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
    Data[Date],
    ,
    ASC
)
  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    I would do this in the query editor by first doing a Group By step on your Customer column and keeping all rows, then add a rank column to those grouped tables with Table.AddRankColumn, and then re-expand all but the Customer column. 

     

    Here is an example.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67DcAgDIThXaiRfOdAcOosEKVF7L9GeBUgpbri0y87Z3cHOu+eN5jVZRRQFIyu+Io4OhKoq0GIhjZwlhMFaQk3o+BqFobZOSy2pYlWU/wdjPtBpuVV7R2vrZt0CDm78gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Product ID" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", type text}, {"Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Customer ID"}, {{"AllRows", each _, type table [Customer ID=nullable text, Product ID=nullable text, Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Ranked", each Table.AddRankColumn([AllRows], "OrderNumber", {"Date", Order.Ascending})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Ranked" = Table.ExpandTableColumn(#"Removed Columns", "Ranked", {"Product ID", "Date", "OrderNumber"}, {"Product ID", "Date", "OrderNumber"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Ranked",{{"Product ID", type text}, {"Date", type date}, {"OrderNumber", Int64.Type}})
in
    #"Changed Type1"

     

     

     

    If you really need to do it with a DAX column, you could use this column expression (replace OrderRanks with your actual table name):

     

    OrderNumberDAX =
    VAR thisdate = OrderRanks[Date]
    RETURN
        CALCULATE (
            COUNTROWS ( OrderRanks ),
            ALLEXCEPT ( OrderRanks, OrderRanks[Customer ID] ),
            OrderRanks[Date] <= thisdate
        )

     

    Pat