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

Column chart from multiple dates

Hi all,

 

I have a table with multiple dates. We want to show per month, in a column chart, how many items were created and closed. Not all items already have a closing date. What's the best way to achive this in Power BI?

 

Capture.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jeltex Member
Member

Re: Column chart from multiple dates

Hi,

 

I've replicated your problem with some dummy data.

Capture.PNG
What you can do is create a date table with:

Calender = CALENDARAUTO()

Then you have to setup the relationships Calendar[date] with table[create date] and an relatoinship on [date] and [closed date].

 

You can then use the following measures:

CountCreate = COUNT(Table1[Create Date])
CountClosed = CALCULATE(COUNT(Table1[Closed Date]),USERELATIONSHIP(Calender[Date],Table1[Closed Date]))

That should work

2 REPLIES 2
Jeltex Member
Member

Re: Column chart from multiple dates

Hi,

 

I've replicated your problem with some dummy data.

Capture.PNG
What you can do is create a date table with:

Calender = CALENDARAUTO()

Then you have to setup the relationships Calendar[date] with table[create date] and an relatoinship on [date] and [closed date].

 

You can then use the following measures:

CountCreate = COUNT(Table1[Create Date])
CountClosed = CALCULATE(COUNT(Table1[Closed Date]),USERELATIONSHIP(Calender[Date],Table1[Closed Date]))

That should work

KevinGesquiere Frequent Visitor
Frequent Visitor

Re: Column chart from multiple dates

@Jeltex: thanks; just what I needed.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 330 members 3,503 guests
Please welcome our newest community members: