summarize and selectcolumns problems

[Anonymous]

Hi. I'm trying to SELECTCOLUMNS() to return a table with a single column [customer1] and then use SUMMARIZE() to count how many rows are present for each customer. This sounds fairly simple but I need to do it each month across a date range that is user-filterable.

I built a small demo model in Power BI to investigate the problem I am having with the "temp1" measure. The DAX is:

temp1 =

SUMMARIZE

(

SELECTCOLUMNS -- build a table with 1 column.

(

FILTER

(

cust_with_multiples,

cust_with_multiples[startdate] <= MAX ( 'date'[Date] )

&& cust_with_multiples[stopdate] > MAX ( 'date'[Date] )

)

,"customer1", cust_with_multiples[customer] -- the column to return from the selectcolumns().

)

, [customer1], "prods_pers_cust", COUNTROWS(cust_with_multiples[customer])

)

The "cust_with_multiples" table contains:

customer	startdate	stopdate
a	01/01/2017	26/12/2018
b	31/01/2017	07/04/2018
c	02/03/2017	16/05/2018
c	01/04/2017	03/04/2018
c	01/05/2017	29/10/2018
f	31/05/2017	30/11/2018
g	30/06/2017	22/11/2018
h	30/07/2017	14/05/2019
i	29/08/2017	30/05/2019
j	28/09/2017	15/12/2018
k	28/10/2017	26/10/2019
l	27/11/2017	08/01/2019

And the "date" table is built by: 

date = CALENDAR(date(2017,1,1), date(2023,1,1))

My desired output from the temp1 measure would be like this:

customer  prods_pers_cust

a     1

b     1

c     3

f      1

....

Thanks.

[Anonymous]

Hi  @Anonymous ,

Please try:

Measure = CALCULATE(COUNT('Table'[customer]),FILTER('Table',[startdate]>=MIN('date'[Date]) && [stopdate]<=MAX('date'[Date])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , check if this between logic can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785