Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KevinGesquiere
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
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

@Anonymous: thanks; just what I needed.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.