Show distribution of Dates in one Column based on another Date Column

[Anonymous]

Hello,

I have two tables:

- Milestones table, with two columns: Planned_Date and Actual_Date 

I have another Calendar Table, with two inactive relations: (One for actual and other for planned)

I need to show the distribution of dates by month, but based on the two columns.

Let me explain: 

If I filter the month "May" in Calendar (with Active Planned_Date), I need to see in which months the Actual Dates (that were planned in May) have their Actual Date.

For example:

Actual_Date	Planned_Date
01/04/2022	01/05/2022
01/05/2022	01/05/2022
01/07/2022	01/05/2022

In this case: With Planned date in May (05) I have one Actual Date in April, One in May and another in July.

Normally, this is very easy to do, as in a Column Chart I only would need to put in X-Axis the Planned_Date.Month and in the Y-Axis, Count(Actual_Date), but has I have a Calendar table, this calculation has becoming in something specially complicated.

I found a solution creating a calculation column and using the column instead, getting the month:

CALCULATE(
    SELECTEDVALUE('Calendar'[Month_Name]),
    CALCULATETABLE(
        Milestones,
        USERELATIONSHIP('Calendar'[Date], Milestones[Planned_Date]),
        REMOVEFILTERS('Calendar')
    )
)

But then I have problems with the Sort...

I think that there must exist a solution using only a single measure without complicating this so much.

Example of desire result:

Thank you!

[ERD]

Hi @Anonymous , you can try this measure:

countDt = 
CALCULATE( COUNT( 'Table'[Planned_Date]), USERELATIONSHIP('Date'[Date], 'Table'[Actual_Date]))

For the chart I used column from Date table, for the filter - 'Table'[Planned_Date]

[Anonymous]

Hi @ERD 
In your case you are filtering directly from Planned Date, I need to filter from Calendar Table, for get the time intelligence columns (.Date / .Month / .Year ...)
That's the problem, that from Milestones Table I don't have access to these Time columns.
Regards.

[ERD]

Ok, one of the ways is to create a second Date table for the slicer, not connected to anything.

The measure: 

countDt = 
CALCULATE( COUNT('Table'[Actual_Date]), 'Table'[Planned_Date] in VALUES('Date_2'[Date]), USERELATIONSHIP('Date'[Date], 'Table'[Actual_Date]))