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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.