Forum Discussion

Jokiamu's avatar
Jokiamu
Regular Visitor
4 years ago
Solved

Filter data of different date column

Hello, 

I wanted to make a report in PowerBI but i not success. I want extract how many item was created and closed by month or year.

 

Here an example of my data : 

 

NameCreatedDateClosedDate

Item1

01/01/2021 
Item201/01/202201/02/2022
Item301/02/2022 
Item401/07/202101/10/2021

 

I would like to have chart bar to know how many item was created in 2021 for example and how many item was closed.

 

 

I want to count how many item had CreatedDate if Date match to my filter date (By year or month). and do the same for closed date.

 

It's easy with 2 separated chart but i need to do it in 1 graph. 

 

I search alternative with "Merge chart" but i failed 😄

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Jokiamu ,

     

    • Method1 : Create a Calendar table firstly:
    Calendar = CALENDAR(MIN('Table'[CreatedDate]),MAX('Table'[ClosedDate]))

    Then create measures:

    Created = CALCULATE(COUNTROWS('Table'),FILTER('Table',YEAR([CreatedDate])=YEAR(MAX('Calendar'[Date])) &&MONTH([CreatedDate])=MONTH(MAX('Calendar'[Date])) ))
    Closed = CALCULATE(COUNTROWS('Table'),FILTER('Table',YEAR([ClosedDate])=YEAR(MAX('Calendar'[Date])) &&MONTH([ClosedDate])=MONTH(MAX('Calendar'[Date])) ))

     

     

    • Method2 : Or you could unpivot the table in Power Query:

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Jokiamu ,

     

    • Method1 : Create a Calendar table firstly:
    Calendar = CALENDAR(MIN('Table'[CreatedDate]),MAX('Table'[ClosedDate]))

    Then create measures:

    Created = CALCULATE(COUNTROWS('Table'),FILTER('Table',YEAR([CreatedDate])=YEAR(MAX('Calendar'[Date])) &&MONTH([CreatedDate])=MONTH(MAX('Calendar'[Date])) ))
    Closed = CALCULATE(COUNTROWS('Table'),FILTER('Table',YEAR([ClosedDate])=YEAR(MAX('Calendar'[Date])) &&MONTH([ClosedDate])=MONTH(MAX('Calendar'[Date])) ))

     

     

    • Method2 : Or you could unpivot the table in Power Query:

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Jokiamu's avatar
    Jokiamu
    Regular Visitor
    4 years ago

    Thanks that's help a lot. 1 calendar table sounds to be the solution. 

     

    I will use method 1. Thanks