Rolling counts-ish

[PBI5851]

hello,

with the below data, i am trying to count how many unique items per month/quarter were open (i.e they must fall between orderdate and maxorderdate)

PriMKey	ItemID	OrderDate	MaxOrderdate
1	abc123	1/15/2021	1/31/2021
2	abc123	1/15/2021	1/31/2021
3	abc123	1/15/2021	1/31/2021
4	def123	1/22/2021	2/16/2022
5	def123	1/22/2021	2/16/2022
6	def123	1/22/2021	2/16/2022
7	def123	1/22/2021	2/16/2022
8	efg123	4/10/2021	7/31/2021
9	efg123	4/10/2021	7/31/2021
10	efg123	4/10/2021	7/31/2021
11	fgh123	11/30/2021	11/30/2021
12	ghi123	12/22/2021	2/16/2022
13	hij123	1/30/2022	2/16/2022
14	hij123	1/30/2022	2/16/2022
15	hij123	1/30/2022	2/16/2022

I hope to achieve two cross tabs (monthly & quarterly)

	jan	feb	march	apr	may	jun	jul	aug	sep	oct	nov	dec
2021	2	1	1	2	2	2	2	1	1	1	2	2
2022	3	3

	Q1	Q2	Q3	Q4
2021	2	2	2	3
2022	2

Ex: For 2021 Apr - i am counting def123 and efg123

For 2021 Nov - I am counting def123, fgh123. 

For Q4 of 2021- I am counting def123, fgh123 and ghi123. 

P.S on the MaxOrderdate, its basically today(). I am trying to use 

UniqueitemCount = CALCULATE( DISTINCTCOUNT('Table'[itemID]), FILTER('Table', 'Table'[Maxorderdatedate].[Date] < ALLSELECTED(Datetable[Date].[Date])))

Any recommendations on how to resolve this please. 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Unique count item: =
CALCULATE (
DISTINCTCOUNT ( Data[ItemID] ),
FILTER (
Data,
MAX ( 'Calendar'[Date] ) >= Data[OrderDate]
&& MIN ( 'Calendar'[Date] ) <= Data[MaxOrderdate]
)
)

[amitchandak]

@PBI5851 , Refe blog on similar topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

File use example with active join to date table