Filter data of different date column

[Jokiamu]

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 : 

Name	CreatedDate	ClosedDate
Item1	01/01/2021
Item2	01/01/2022	01/02/2022
Item3	01/02/2022
Item4	01/07/2021	01/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

[Jokiamu]

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

I will use method 1. Thanks

[Anonymous · Accepted solution]

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.