Count based on multiple criteria

[Yggdrasill]

I have this demotable. ColumnB has many different values, as well as Column C. Disregard ColumnA

ColumnA	Date	ColumnB	ColumnC	Calculate count
07	21.9.2017	1000	A	1
07	22.9.2017	1000	A
07	16.11.2017	1000	A	2
07	17.11.2017	1000	A
07	8.1.2018	1000	A	3
07	9.1.2018	1000	A
07	12.1.2018	1000	A	4
07	1.6.2018	1000	A	5
07	3.10.2018	1000	A	6
07	4.10.2018	1000	A
07	3.1.2019	1000	A	7
07	11.2.2019	1000	A	8
07	12.2.2019	1000	A

My desired output is the Calculate count. I do not want to count the rows where the criteria meets if it has been counted for the previous day.

My effort so far is this

Calculate count =
CALCULATE (
    COUNTROWS(DemoTable);
    FILTER (
        DemoTable;
        DemoTable[ColumnB] = EARLIER ( DemoTable[ColumnB] )
            && DemoTable[Date] <= EARLIER ( DemoTable[Date] )
            && DemoTable[ColumnC] = 1000
    )
)

This returns 1 - 13 events so it is not filtering out the sequental days.

Any help here would be greatly appreciated.

[Yggdrasill]

Going back to this problem.

I have defined a holiday table which is merged to my date table. I want to exclude holidays, saturdays and sundays from my counter but doesn't the function EARLIER() expect consecutive dates ?

Example of the output (Correct Calc count)

ColumnA	Date	ColumnB	ColumnC	Calculate count		Correct Calc count	Day of week
7	21.9.2017	1000	A	1		1	5
7	22.9.2017	1000	A				6
7	16.11.2017	1000	A	2		2	5
7	17.11.2017	1000	A				6	Friday
NEW LINE	20.11.2017	1000	A	3			2	Monday
7	8.1.2018	1000	A	4		3	2
7	9.1.2018	1000	A				3
7	12.1.2018	1000	A	5		4	6
7	1.6.2018	1000	A	6		5	6
7	3.10.2018	1000	A	7		6	4
7	4.10.2018	1000	A				5
7	3.1.2019	1000	A	8		7	5
7	11.2.2019	1000	A	9		8	2
7	12.2.2019	1000	A				3

I tried to exclude the holidays and weekends from my calculations but I'm not getting the desired "correct" output 

[LivioLanzo]

@Yggdrasill 

I think you just need to change the 'AddCol1' formula to:

AddedCol1 = 
var subDays = IF( Data[Day of Week] = 2, 3, 1 )
return
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] =  EARLIER( Data[Date] ) - subDays
    )
) + 0

[Yggdrasill]

That could work well in case of mondays. However, I'm dealing with holidays too !

I have a Holiday table merged to my datetable so all holiday rows are marked with 0 and 1 if their active. I basically need to count the days between the last active day and current active day and use that variable for the subDays

[LivioLanzo]

Hi @Yggdrasill 

are you able to post some sample data with your date table included?

[LivioLanzo · Accepted solution]

Hi @Yggdrasill

you can do thi sin 2 steps:

first add this column:

AddedCol1 = 
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] = EARLIER( Data[Date] ) - 1 
    )
) + 0

and then this final column:

AddedCol2 = 
IF(
    Data[AddedCol1] = 0,
    BLANK(),
    CALCULATE(
        SUM( Data[AddedCol1] ),
        ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ),
        Data[Date] <= EARLIER( Data[Date] )
    )
)

[Yggdrasill]

You absolute beauty !

Thank you !