Forum Discussion

KevinGesquiere's avatar
KevinGesquiere
Frequent Visitor
7 years ago
Solved

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?

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi,

     

    I've replicated your problem with some dummy data.


    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    I've replicated your problem with some dummy data.


    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