count of how many dates match

[Anonymous]

Hello, 

I have a table that I am essentially trying to flatten out, It looks like this

Planned Date	Actual Date
1/1/21	1/3/21
1/4/21	1/3/21
1/5/21	1/5/21

I have created a 2nd table as my calander and I would like to have the count of the above dates;

Calander Date	Planned Count	Actual Count
1/1/21	1	0
1/2/21	0	0
1/3/21	0	2
1/4/21	1	0
1/5/21	1	1

Any tips on how to make the planned count and actual count columns?

Thanks!

[VahidDM]

hi @Anonymous 

Try these measures:

Count Of Date Actual =
VAR _Date =
    MAX ( 'Calendar Table'[Calander Date] )
RETURN
    VAR _ActualDate =
        CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Actual Date] = _Date )
    RETURN
        IF ( ISBLANK ( _ActualDate ), 0, _ActualDate )

Count Of Date Planned =
VAR _Date =
    MAX ( 'Calendar Table'[Calander Date] )
RETURN
    VAR _PlannedDate =
        CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Planned Date] = _Date )
    RETURN
        IF ( ISBLANK ( _PlannedDate ), 0, _PlannedDate )

The output sill be as follow:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[Fowmy]

@Anonymous 

You can build a calculated column as follows and follow the same for the actual as well:

Planned Date Count = 
VAR __DATE = TABLE2[DATE] RETURN
COUNTROWS(
    FILTER( VALUES(table1[planned date]), table1[planned date] = __DATE )  
)

[Anonymous]

@Fowmy Thanks! This is working well but what if I have 2 items with the same date? It is just returning a '1' in these cases. How can I modify to count all the matching dates?

[Migasuke]

Hi @Fowmy ,

How did you manage to put column in variable?

VAR __DATE = TABLE2[DATE]

In my case never works.

Thanks.
 

[Fowmy]

@Migasuke 

Yes, I mentioned it as a measure, it should be a calculated column. I changed the word in my reply.

Thanks