cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## 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 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

2 ACCEPTED SOLUTIONS
Resolver III

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.

Super User

@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
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
3 REPLIES 3
Super User

@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
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Regular Visitor

Thank you so much Fowmy,

This worked a treat, I can sleep tonight.

Resolver III

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.