count total occurrences with filter by category

[dwhittaker1]

Id like to create a measure or calculated column that calculates the total occuerrence of  early for each item. For example, in the table below "Item A" would have a value of 2 because "early" appears two times with "Item A". "Item B" would have a value of 0 because early does not apply. "Item C" will have 3 because early appears three times

SalesOrder #	Salesorderline#	Create Date	Invoice Date	Item	Segment	Site	Status
100	1	1/1/2024	1/6/2024	A	front	Bank	Early
100	2	2/1/2024	2/6/2024	A	front	School	Early
101	1	1/2/2024	1/7/2024	A	back	Kitchen	Late
101	2	2/2/2024	2/7/2024	B	side	Park	Late
102	1	3/1/2024	3/6/2024	C	back	House	Early
102	2	4/1/2024	4/6/2024	C	front	Store	Early
102	3	5/1/2024	5/6/2024	C	min	Mall	Early
103	1	2/1/2024	2/6/2024	D	max	Store	Early

Desired output

SalesOrder #	Salesorderline#	Create Date	Invoice Date	Item	Segment	Site	Status	count of early
100	1	1/1/2024	1/6/2024	A	front	Bank	Early	2
100	2	2/1/2024	2/6/2024	A	front	School	Early	2
101	1	1/2/2024	1/7/2024	A	back	Kitchen	Late	2
101	2	2/2/2024	2/7/2024	B	side	Park	Late
102	1	3/1/2024	3/6/2024	C	back	House	Early	3
102	2	4/1/2024	4/6/2024	C	front	Store	Early	3
102	3	5/1/2024	5/6/2024	C	min	Mall	Early	3
103	1	2/1/2024	2/6/2024	D	max	Store	Early	1

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item    ]=EARLIER(Data[Item    ])&&Data[Status   ]="Early"))

Hope this helps.

[dwhittaker1]

• @Ashish_Mathur  thank you for your help, how would I add a filter to your measure that returns only returns results for the year 2024

[Fowmy]

@dwhittaker1 

Add this calculated column:

Early Count = 
CALCULATE(
    COUNTROWS(Table01),
    ALLEXCEPT( Table01 , Table01[Item]),
    Table01[Status] = "Early"
)

[dwhittaker1]

• @Fowmy thank you for your help, how would I add a filter to your measure that returns only returns results for the year 2024

[Fowmy · Accepted solution]

@dwhittaker1 

Add an additional filter to your calculated column as follows, I assume you need the year from the Creation Date. you can change it to a different column as well.

Early Count = 
CALCULATE(
    COUNTROWS(Table01),
    ALLEXCEPT( Table01 , Table01[Item    ]),
    Table01[Status   ] = "Early",
      YEAR( Table01[Create Date   ]) = 2024
)