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 :    Name CreatedDate Clos...
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.

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