Forum Discussion

MG25's avatar
MG25
Regular Visitor
5 years ago
Solved

Filter by date periods

Hi,

 

I'm new to Power BI and I am hoping someone could help me out with what I thought would have been simple. It turns out it's not so simple if you don't know how.

 

I have a query that has a list of Product repair numbers, date the products were returned and when they were originally manufactured. 

 

What I can't figure out is how to calculate 'of all the products returned in May, how many of them are less than a year old.

Product Return NumberProduct Return DateProduct Date of Manufacture
2744401/05/202107/01/2021
2744509/05/202107/01/2021
2744617/05/202107/01/2021
2744725/05/202116/03/2020
2744829/05/202116/03/2020
2862501/06/202107/01/2021
2862609/06/202107/01/2021
2862717/06/202116/03/2020
2974301/07/202107/01/2021
2974409/07/202116/03/2020
2974517/07/202116/03/2020
2974625/07/202116/03/2020

 

I also need the data to be displayed in a matrix like this:

May-21Jun-21Jul-21
321

 

Any help with this would be much appreiciated

Need Help
  • Portrek's avatar
    Portrek
    5 years ago

    hi, MG25

    You can use the measure below

     

     

    measure = 
    CALCULATE(
        COUNTROWS('Table'),
            DATEDIFF('Table'[Product Date of Manufacture],
                'Table'[Product Return Date],
                DAY
        )<365
)

     

     

    best regards.

  • Fowmy's avatar
    Fowmy
    5 years ago

    MG25 

    I recommend you to create a date table in your model if you don't have one and create a relationship. Add the following measure and insert a Matrix visual.  I attached a sample file.

    Less than one year = 
COUNTROWS(
    FILTER(
        Table5,
        INT( Table5[Product Return Date]  -  Table5[Product Date of Manufacture] ) < 365
    )
)

     

3 Replies

  • Portrek's avatar
    Portrek
    Icon for Resolver III rankResolver III
    5 years ago

    hi, MG25

    You can use the measure below

     

     

    measure = 
    CALCULATE(
        COUNTROWS('Table'),
            DATEDIFF('Table'[Product Date of Manufacture],
                'Table'[Product Return Date],
                DAY
        )<365
)

     

     

    best regards.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    MG25 

    I recommend you to create a date table in your model if you don't have one and create a relationship. Add the following measure and insert a Matrix visual.  I attached a sample file.

    Less than one year = 
COUNTROWS(
    FILTER(
        Table5,
        INT( Table5[Product Return Date]  -  Table5[Product Date of Manufacture] ) < 365
    )
)

     

    • MG25's avatar
      MG25
      Regular Visitor
      5 years ago

      Thank you so much Fowmy,

       

      This worked a treat, I can sleep tonight.