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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.