Count of dates in another column

[Blizzardstyx]

Hi all,

Need help with calculating number of times that a date shows up in another date column. I also need it to be filtered for a specific tractor ID

The two columns are [Pickup date] and [return date] and all I need to do is figure out how many times that a specific return date is found in the [Pickup date] column. It might just be an earlier function, but I just can't seem to get it right.

Here's an example table

Tractor ID	Activity ID	Pickup Date	Return Date	Count** (column I need)
5	1	1.1.2020	1.12.2020	2
5	2	1.12.2020	1.12.2020	2
5	3	1.12.2020	1.30.2020	0
10	4	1.30.2020	1.30.2020	1
10	5	1.31.2020	2.5.2020	0
10	6	1.31.2020	1.31.2020	2

I have an example file here for people to work out of:

https://drive.google.com/file/d/1kDeDBJzJK2NXXWbV9uYqJs_TzRVjs1qd/view?usp=sharing

Thanks in advance!

Thank you in advance

[v-alq-msft · Accepted solution]

Hi, @Blizzardstyx 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as follows.

Count = 
var _tractorid = MAX('Table'[Tractor ID])
var _returndate = MAX('Table'[Return Date])
var _count = 
COUNTROWS(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Tractor ID] = _tractorid&&
        'Table'[Pickup Date] = _returndate
    )
)

return
IF(
    ISBLANK(_count),
    0,
    _count
)

Result:

Best Regards

Allan

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

[Anonymous]

Hey @Blizzardstyx 

I'm not sure I'm understanding. You're wanting to know how many times there were returns on a pickup date or how many items on a specific pickup date were returned later?

[Blizzardstyx]

Hi @Anonymous ,

Just modified the question above. Hope it makes more sense.

Thank you!

[Anonymous]

Ah. I see now. That is simple. You just need the countx function:

https://docs.microsoft.com/en-us/dax/countx-function-dax

Try Count = COUNTX(Table[Pickup Date], Table[Return Date])

set it up as a calculated column and change the "Table" in the formula. Let me know if it works.

If this helps please Kudo. 

If this solves your problem please accept it as a solution.

[Blizzardstyx]

I don't believe I can put 'Table[Pickup Date]' as the first argument of a countx formula. It asks for a table, not column.

[Anonymous]

Hey @Blizzardstyx 

You are correct. You will need to create a duplicate table (aka a reference query), remove the other columns, then remove duplicate rows, and create the measure so that your pickup date and return are in separate tables.

You can also do it in query editor as shown in the sample file in this thread: https://community.powerbi.com/t5/Desktop/Count-If-from-the-same-table/td-p/531223

or this one: https://community.powerbi.com/t5/Desktop/COUNTIF-in-Query-Editor/td-p/657700