cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lumegu
Frequent Visitor

Plot count of two different date sets on single date axis

Dear community,

 

I have a question which I think should be easy to solve, but I simply can't think of / find a smart solution so far.

 

I have the following dataset:

 

SubjectPlanned dateDate ready
Document 11-2-20201-2-2020
Document 21-5-20201-8-2020
Document 31-2-20201-5-2021
Document 41-8-2020 
Document 51-5-20211-5-2021
Document 61-2-20211-2-2021
Document 71-2-20211-5-2021
Document 81-11-20201-11-2020
Document 91-11-2021 
Document 101-2-20201-5-2021

 

What I would like to visualize is very simple: a bar chart in which is shown how many documents have been planned for review every quarter (planned date) and how many have been finished (date ready) in that quarter. However, if I pick either one of the date columns as the x-axis the result is columns of equal hight, I somehow need a "neutral" date axis to show the count of the planned and ready amounts per quarter.

 

Is it possible to do this with a measure or should I maybe create a helping table?

 

Many thanks!

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

First, create a calendar table as the X axis of bar chart .

Then create measures like below:

planned_ = CALCULATE(COUNT('Table'[Subject]),FILTER('Table','Table'[Planned date]<=MAX('Table 2'[Date])&&'Table'[Planned date]>=MIN('Table 2'[Date])))
ready = CALCULATE(COUNT('Table'[Subject]),FILTER('Table',NOT(ISBLANK('Table'[Date ready]))&&'Table'[Date ready]<=MAX('Table 2'[Date])&&'Table'[Date ready]>=MIN('Table 2'[Date])))

Vlianlmsft_0-1632724659690.png

 

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

First, create a calendar table as the X axis of bar chart .

Then create measures like below:

planned_ = CALCULATE(COUNT('Table'[Subject]),FILTER('Table','Table'[Planned date]<=MAX('Table 2'[Date])&&'Table'[Planned date]>=MIN('Table 2'[Date])))
ready = CALCULATE(COUNT('Table'[Subject]),FILTER('Table',NOT(ISBLANK('Table'[Date ready]))&&'Table'[Date ready]<=MAX('Table 2'[Date])&&'Table'[Date ready]>=MIN('Table 2'[Date])))

Vlianlmsft_0-1632724659690.png

 

View solution in original post

Brilliant, this was exactly what I was looking for. Only remaining question will be how to manage interactions if you add a table as a visual. How do you manage relations with Table 1 and table 2 in this case, you have to pick a column from table 1 (planned or ready) to connect with date in table 2. Which than makes it impossible to click on only the planned or a ready column in the bar chart, but you always open both against the chosen relationship.

 

Hope the explanation above makes sense, if not please let me know!

 

 

amitchandak
Super User
Super User

@Lumegu , You need to create a common date table and join with both dates. You can use userelationship wo activate inactive relationship

 

refer smiliar example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.