count rows on summarize column filtered by date measure

[Anonymous]

Hi guys,

I am strugling to create a measure which will be be filtering by date slicer coming from a calendar table. My data are like these:

Codes	date	group
X0001	1/2/2020	A
X0002	1/3/2020	A
X0002	1/4/2020	B
X0004	1/5/2020	A
X0005	1/6/2020	A
X0006	1/7/2020	A
X0006	1/8/2020	A
X0008	1/9/2020	A
X0009	1/10/2020	A
X0010	1/11/2020	A
X0010	1/12/2020	B
X0012	1/1/2021	A
X0013	1/2/2021	A
X0014	1/3/2021	A
X0015	1/4/2021	A
X0015	1/5/2021	B
X0017	1/6/2021	A
X0018	1/7/2021	A
X0019	1/8/2021	A
X0020	1/9/2021	A
X0021	1/10/2021	A
X0022	1/11/2021	A

the above table is linked with a calendar table.

What I am trying to do is to find on a card how many Codes have group A and groub B but also to be able  to filter from a date slicer the dates that is looking for. 
Example if the slicer is between 01/01/2021 and 31/12/2021 the result should be 1 since between 01/01/2021 and 31/12/2021 only code "X0015" has group A and B. If the date slicer if  from 1/4/2020 then the result should be 2 ("X0010","X0015" have both groups).

I have tried the below but i can't put the date slicer:

Counttable =

CALCULATETABLE(

SUMMARIZECOLUMNS(

Sheet1[Codes],

"count", [count_group])) 

[v-lionel-msft]

Hi @Anonymous ,

My date format is mm/dd/yyyy, is it caused by this?

Best regards,
Lionel Chen

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

[Anonymous]

Yes, this cause the problem. But when I am tring to change to lacal format (D/M/YYYY) then the calculation shows wrong number.

From 1st of December 2020 until 1st of November of 2021 only one code(X0015) have both A and B group.

[amitchandak]

@Anonymous , Try a measure like

countx(filter(summarize(Table, Table[Codes], "_1", calculate(distinctcount(Table[group]), filter(Table, Table[group] in {"A", "B"}))), [_1] =2),[Codes])

[Anonymous]

Thank you, man, for your quick response. At least I am looking from the right side.
It is almost what I did. My formula is
countx(filter(summarize(table,table[codes], "_1", distinctcount(group)),distinctcount(group)>1,max(Calendar[Date]))
Now I have one more step, to replace the "max('Calendar'[Date])" with dates between 2 other measure dates and not from the date.

[v-lionel-msft]

Hi @Anonymous ,

Try to refer to my .pbix file.

Best regards,
Lionel Chen

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

[Anonymous]

Thank you @v-lionel-msft ,

but in your case you count the group of "A","B" in the given period. What i am looking is in a date range (which the start_date and end_date is a calculated measure of a calendar table with some extra calculations) to see how many codes have group "A" and "B". In your pbix if in the dates put from 12/1/20 until 11/1/21 the result you get is 2 instead of 1 which is the correct. 

[v-lionel-msft]

Hi @Anonymous ,

I don't particularly understand what you said.
My calculation logic is:
During the period from 12/1/2020 to 1/11/2021, only X0015 is counted.

What is the calculation logic you want to realize?

Best regards,
Lionel Chen

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

[Anonymous]

Thank you for your reply.

Have you changed anything? my result in your pbix is as per below (date format d/m/yyyy)