Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
@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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you so much Fowmy,
This worked a treat, I can sleep tonight.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.