Filter by date periods

[MG25]

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 Number	Product Return Date	Product Date of Manufacture
27444	01/05/2021	07/01/2021
27445	09/05/2021	07/01/2021
27446	17/05/2021	07/01/2021
27447	25/05/2021	16/03/2020
27448	29/05/2021	16/03/2020
28625	01/06/2021	07/01/2021
28626	09/06/2021	07/01/2021
28627	17/06/2021	16/03/2020
29743	01/07/2021	07/01/2021
29744	09/07/2021	16/03/2020
29745	17/07/2021	16/03/2020
29746	25/07/2021	16/03/2020

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

May-21	Jun-21	Jul-21
3	2	1

Any help with this would be much appreiciated

[Portrek · Accepted solution]

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 · Accepted solution]

@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]

Thank you so much Fowmy,

This worked a treat, I can sleep tonight.

[Portrek · Accepted solution]

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.