Clustered Column comparing count of dates in two columns from same table

[AaronHL]

I have two date columns in a table, "date1" and "date2". Not every row has both columns populated. I wish to be able to compare the count of those dates for each month/year using a clustered column visual. I'm unsure how I can do this.

I've tried using just "date1" as the x-axis and then putting both the count of "date1" and "date2" as the y-axis. This just leads to the "date1" columns in the visual to display the way I want, but "date2" columns in the visual just represent the count of rows where "date2" is populated for given "date1" date in the same row (and lots of "date2" just comes back as "blank" for the rows where "date1" is populated but "date2" isn't).

Any help would be appreciated.

[Ashish_Mathur]

Hi,

Your requirement is not clear.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[amitchandak · Accepted solution]

@AaronHL , Join both date1 and date 2 to a common date table. Make sure dates do not have timestamp. One join will be inactive, which you can activate on measure using userelationship

Assume date 2 is inactive

calculate( SUM(Table[Amount]),USERELATIONSHIP ('Table'[date2], 'Date'[Date]))

Now use date from date on axis

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[AaronHL]

I was hoping ot do this without creating a new table, but yah I think this is the only way it can be done. Thank you.