Custom column - Count of entries with a given filter per day

[Anonymous]

Hello,

I'm using Power BI to manage Timesheet reports.

I need to create a custom column which counts the number of entries per day for a given client. Once I have that number, I need to substract a total of 45min per day spread over all entries of that client.

Here is some sample data:

date	client	hours
06.10.2019	A	1
06.10.2019	A	2
06.10.2019	B	1.5
06.10.2019	A	3
06.10.2019	C	0.5
07.10.2019	A	4
07.10.2019	A	1
07.10.2019	C	3
08.10.2019	A	3
08.10.2019	D	1
08.10.2019	A	2
08.10.2019	B	0.5
08.10.2019	A	1.5

What I'm looking to get is this (using Client A for the count) :

date	client	hours		count_per_day	hours v2
06.10.2019	A	1		3	0.75
06.10.2019	A	2		3	1.75
06.10.2019	B	1.5		3	1.5
06.10.2019	A	3		3	2.75
06.10.2019	C	0.5		3	0.5
07.10.2019	A	4		2	3.625
07.10.2019	A	1		2	0.625
07.10.2019	C	3		2	3
08.10.2019	A	3		3	2.75
08.10.2019	D	1		3	1
08.10.2019	A	2		3	1.75
08.10.2019	B	0.5		3	0.5
08.10.2019	A	1.5		3	1.25

The hours v2 is simple to get once I get the Count/day :

IF(Client = "A" THEN hoursv2 = hours-0.75/count_per_day ELSE hours)

How can I achieve this count as a new column ?

Thanks !

[Zubair_Muhammad · Accepted solution]

@Anonymous 

As  a calc column you can use

Column =
CALCULATE (
    COUNTROWS ( TableName ),
    ALLEXCEPT ( TableName, TableName[Dates] ),
    TableName[client] = "A"
)

[Zubair_Muhammad · Accepted solution]

@Anonymous 

As  a calc column you can use

Column =
CALCULATE (
    COUNTROWS ( TableName ),
    ALLEXCEPT ( TableName, TableName[Dates] ),
    TableName[client] = "A"
)

[Zubair_Muhammad · Accepted solution]

@Anonymous 

As a custom Column in Power Query

=Table.RowCount(
Table.SelectRows(
    #"Changed Type",
     (x)=> x[date]=[date] and x[client]="A"
                )
               )