Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
MG25
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 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

2 ACCEPTED SOLUTIONS
Portrek
Resolver III
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.

View solution in original post

Fowmy
Super User
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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

MG25
Regular Visitor

Thank you so much Fowmy,

 

This worked a treat, I can sleep tonight.

Portrek
Resolver III
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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.