Group data by before a date

[martcatnip]

I am trying to make a graph that shows all the devices that have appeared in the graph before a specific date.

For example, my original data is: with the first column as date and second as device name

7/21/2021	b1
7/21/2021	b2
7/22/2021	b1
7/22/2021	b3
7/23/2021	b4
7/24/2021	b1
7/24/2021	b2

For 7/21/2021, the result should be b1, b2;

for 7/22/2021 the result should be b1, b2, b3. Although there is no record for b2 on 7/22/2021, there is a record for it on the previous day so we also include it here;

for 7/23/2021 the result should be b1, b2, b3,b4;

for 7/24/2021 the result should also be b1, b2, b3,b4.

Thank you so much for your help!

[Greg_Deckler · Accepted solution]

@martcatnip Not exactly certain how you want the return data, but this is essentially a Complex Selector, The Complex Selector - Microsoft Power BI Community

Maybe something like:

Measure = 
  VAR __SelectedDate = MAX('Table'[Date])
  VAR __Table = FILTER(ALL('Table'),[Date]<=__SelectedDate)
  VAR __Devices = DISTINCT(__Table,[Device])
RETURN
  CONCATENATEX(__Devices,[Device],",")