Calculate failure rating in specific periods

[Syndicate_Admin]

I need help calculating failure ratings for specific dates. The query I have calculates the failure rating for each part number taking into account the Total Usage vs Total Warrantied, but does not calculate it for a specific month, year, or other period of time.

The Syntax used is:

% Total Failed =

var partsfailed = SUM(Warranty[Part Qty])

var totalparts = SUM(Usage[USAGE])

var rate = partsfailed/totalparts

return IFERROR(rate,0)

These are the results of the query

This is data #1

Data #2

Relationships

Date filters in the report

I think the problem is that the "usage" of the part is not being calculated correctly for the specified period but always takes the "total usage", although it is calculating the correct number of failed parts in that time.

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data of table Warranty and Usage (exclude sensitive data) and list some special examples with incorrect value to explain it with calculation logic and related screenshot? By the way, the applied field on that two date slicers from the table Warranty and Usage? It is better if you can share a simple sample pbix file with me in order to make troubleshooting and provide you a suitable solution. Thank you.

Best Regards

[Anonymous]

i sent you a private message with my data

[v-yiruan-msft]

Hi @Anonymous ,

I’m sorry to tell you that we could only offer technical support on the Power BI Community for security reason. You can refer the following link to share the file with me.  Also, you can paste your data with Text format directy in your post just like below table. Please exclude the sensitive data before share it with me. Thank you.

How to upload PBI in Community

Col1	Col2	Col3
xx	xx	xx
xx	xx	xx

Best Regards

[Syndicate_Admin]

https://docs.google.com/file/d/1oy83LicUZZPWKUqAl0XjQ37ZKBWyWRgd/edit?usp=docslist_api&filetype=msex...

https://drive.google.com/file/d/16CPmbIZ2AfziTWFN4AoELt1ATQz7qQ4p/view?usp=drivesdk

Thank you

[v-yiruan-msft]

Hi @Anonymous ,

After looking at your pbit file, it turns out that you have two date slicers set up in your report and they come from the same table (Warranty table) in the Created On and MFG. on fields. The field that connects the Usage and Warranty tables is party#, thus when using the date slicer, it will filter out the part# that filters the corresponding date period, and the Usage table will only come up with the corresponding part# info. I checked the data and found that usage also shows the Usage value of the filtered part#, but not total usage values. Also, I found a date field in the table Usage, do you want its data to be displayed according to the selected date range?

Best Regards