Search code examples
powerbidaxcalculated-columns

Identify the first occurrence of a record by monthly basis calculated column

I have a requirement, where i need to create a calculated column and to identify the first occurrence of a record by monthly basis.

Below is the sample data. The data has duplicates values o a monthly basis.

ID   MONTH
AA   Jan-01
AA   Jan-02
BB   Feb-10
BB   Feb-20

Expected output:

ID  Month   out    
AA  Jan-01   1
AA  Jan-02   0
BB  Feb-10   1
BB  Feb-20   0

Note: If i use powerquery to remove duplicate values, i can achieve this. But i need to achieve this in calculated column.

Solution

  • To solve this you can do the following:

    1. Calculate a "Year-Month" column from your date (or two separate columns, "year" and "month")
    2. clone or reference your current query
    3. in this clone use the Group By (in the transform tab)
      • use the advanced section and group by ID and Year-Month
      • calculate the Min of "Date" (let's call this col DateKey)
    4. This will produce a table with the first occurrence by ID and Year-Month and the min date (first occurrence)
    5. Join (merge queries) the original table and the aggregate one by ID and DateKey
    6. To create your custom column "out" you have several options
      • create a static column always valued to 1 in the aggregated table, and add it in the join
      • create a column after the join, which checks if any column if any of the columns in the joined table has a value or is null and the set a value. the M formula looks like this Table.AddColumn(#"Expanded Table (2)", "out", each if [#"AggregatedTable.id"] = null then 0 else 1, type number)