PowerBI DAX measure to calculate duration of assignment based on just one date field (Assign date). End date is when value stops appearing

Question

I have trouble putting together the logic to solve the following with DAX measures.

I have a 'Facts table' with three columns: [Assign_Date] in date format, [Person ID] text and [Assignement ID] text. None of the values is unique and theres a relationship between [Assign_Date] and Calendar[Date].

eg.

Assign_Date	Person ID	Assignement ID
09 September 2023	Person A	XY1
09 September 2023	Person B	XY55
16 September 2023	Person B	XY55
23 September 2023	Person B	XY1
23 September 2023	Person C	XY89
30 September 2023	Person D	XY1
30 September 2023	Person D	XY89

Assign_date is a fixed date each week. (eg. Every saturday the last 5 years). People do concecutive dates and we don't need to consider a scenario where they re-appear after some time. Each Person ID starts an Assignement ID on the earliest [Assign_date] they appear on the table. There are no end dates. They remain assigned, and their last day is assumed the date when there is no longer a record of their User ID. Eg. Last date for person B is 30 September (duration 3 weeks). In a more granular level, Last date for person B in Assignement XY55 is 23 september (duration 2 weeks). The only order that matters for assignements is date order.

a) It is most important for me to calculate the interval in days (or weeks) between the earliest date a User ID appeared on the table and the date it stopped appearing. No Assignement detail needed for this calculation.

b) I'd like a list which user IDs completed all their assignements each month of the year. Assuming something to do with CONCATINATEX.

c) Nice to have the interval in days (or weeks) where a User ID remained assigned to a specific Assignement ID.

I have tried playing with DATEDIFF, SUMX, EARLIER AND CONCATINATEX but quite early in my DAX journey and I've not been able to create the right syntax to solve this

Many thanks in advance.

Answer 1

Try the following in PowerQuery to simplify your Facts table.

let
  Source = YOUR_SOURCE,
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Assign_Date", type date}, {"Person ID", type text}, {"Assignement ID", type text}}),
  dateList = List.Distinct(#"Changed column type"[Assign_Date]),
  #"Grouped rows" = Table.Group(#"Changed column type", {"Person ID", "Assignement ID"}, {{"Date Assigned", each List.Min([Assign_Date]), type nullable date}, {"Last Date", each List.Max([Assign_Date]), type nullable date}}),
  #"Added custom" = Table.AddColumn(#"Grouped rows", "Date Completed", each 
    let
      thisD = [Last Date],
      res = List.First(List.Select(dateList, each _ > thisD))
    in
      res
  , type date),
  #"Removed columns" = Table.RemoveColumns(#"Added custom", {"Last Date"})
in
  #"Removed columns"

This will give you a table where there is only one row per Person per Assignment:

Person ID	Assignement ID	Date Assigned	Date Completed
Person A	XY1	9/9/2023	9/16/2023
Person B	XY55	9/9/2023	9/23/2023
Person B	XY1	9/23/2023	9/30/2023
Person C	XY89	9/23/2023	9/30/2023
Person D	XY1	9/30/2023	null
Person D	XY89	9/30/2023	null

Load the query into your data model, and I suggest not having a relationship between this table and your Calendar table. The following assumes there isn't a relationship between them.

---

Create a Measure for (a):

Assigned duration - days = 
  var result =
    SUMX(
      DISTINCT('Facts table'[Person ID]),
      DATEDIFF(
        MIN('Facts table'[Date Assigned]),
        COALESCE(MAX('Facts table'[Date Completed]), TODAY()),
        DAY
      )
    )

  return result

---

Create a Measure for (b):

Users completed = 
  var filteredDateMin = MIN(Calendar[Date])
  var filteredDateMax = MAX(Calendar[Date])

  var result =
    CALCULATE(
      CONCATENATEX(
        DISTINCT('Facts table'[Person ID]),
        [Person ID],
        ", "
      ),
      filteredDateMin <= 'Facts table'[Date Completed] &&
      'Facts table'[Date Completed] <= filteredDateMax &&
      NOT ISBLANK('Facts table'[Date Completed])
    )

  return result

---

Create a Measure for (c):

Assigned duration - not completed - days = 
  CALCULATE(
    [Assigned duration - days],
    FILTER('Facts table', [Date Completed] = BLANK() )
  )