Concatenate and summarize a field based on dynamic input

[MrJames76]

Hi all,

I'm trying to figure out if I can pass a date from a date slicer into a measure, calculated field, or calculated table that concatenates the relevant rows and allows me to count/sum other measures.

What I have is a data set that lists customer subscriptions for multiple products with start and end dates.

Account_Name	Product	Start_Date	End_Date	ARR
A	Product X	1/1/2021	12/31/2021	100
A	Product Y	5/1/2021	12/31/2021	50
B	Product X	5/1/2021	5/30/2022	60
C	Product Y	1/1/2021	12/31/2021	100
D	Product X	1/1/2021	12/31/2021	60
D	Product Y	1/1/2021	12/31/2021	80

Using a date slicer I can create a measure that summarizes ARR at any point in time:

Current_ARR = var _selecteddate = MAX('Dates[Date]) return CALCULATE(SUM(ARR), Start_date <= _selecteddate, End_date >= _selecteddate)

What I'd like to be able to do is create a table that summarizes things by product ownership. For example, assuming the date was set to 6/1/2021:

Products_Owned	Account_Count	Total_ARR
Product X, Product Y	2	290
Product X	1	60
Product Y	1	100

However, if the date in the slicer changed to 4/1/2021 it would show:

Products_Owned	Account_Count	Total_ARR
Product X, Product Y	1	140
Product X	2	160
Product Y	1	100

I've tried: 

Products_Owned =

var _selecteddate = MAX('Dates'[Date])

return

CALCULATE(

   CONCATENATEX(

      SUMMARIZE(

            Filter(DATA, END_DATE >= _selecteddate && START_DATE <=_selecteddate)

, PRODUCT)

, PRODUCT ,", "))

However, since it's a measure (I guess) I'd need to have the table showing each customer and am unable to summarize the count of customers against each product combination. Otherwise the output is just all the Products combined.

Any thoughts are welcome!

[danextian]

Hi @MrJames76 ,

By "table", do you mean as a visual or a calculated table? The latter is static and will not change until you modify the formula behind or refresh the dataset.

[MrJames76]

Thanks danextian! I mean as a visual (I think). At it's simplest there are only two elements on the tab/page - a date slicer which defines the specific date I'm interested in, and a table or matrix that shows the product combinations, customer count, and ARR.

[Ashish_Mathur]

Hi,

Is it fair to assume that you will select a particular month (such as April 2021) or do you want to be able to select a spsecific date such as April 23, 2021?  Please clarify.

[MrJames76]

Ideally I can pick a particular date, but if that's not reasonable I could live with a period - would just have to decide on date within the month presents the month (i.e., if a customer cancels on April 15th are they counted in April or not - very much a run-rate analysis vs. a recognized revenue analysis).

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution workbook from here.

Hope this helps.