Forum Discussion
[Help] Creating Monthly Test frequency visualization (Year-Independent)
Test Items overview
I have test items, and each item includes the following information:
Coverage: Two types, Front and Back.
Test Frequency: Six different intervals: Quarterly, Bi-Annually, Annually, Every 2 years, Every 3 years, and Every 5 years.
Test Date: The date when the test was executed.
Ex)
Item Test Coverage Test Frequency Test Date Unit1 Back Bi-Annually 2025-03-11 Unit1 Front Quarterly 2024-08-04 Unit1 Front Quarterly 2024-11-11 Unit1 Front Quarterly 2025-01-24 Unit1 Front Quarterly 2025-06-04 Unit2 Back Annually 2022-03-09 Unit2 Back Annually 2023-09-16 Unit2 Back Annually 2024-10-25 Unit2 Front Quarterly 2025-01-07 Unit2 Front Quarterly 2025-04-03 Unit2 Front Quarterly 2025-07-03 Unit3 Back Every 2 years 2024-05-07 Unit3 Front Every 3 years 2023-09-07 Unit4 Back Annually 2022-08-04 Unit4 Back Annually 2023-05-07 Unit4 Back Annually 2024-06-07 Unit4 Back Annually 2025-04-07 Unit4 Front Bi-Annually 2024-08-09 Unit4 Front Bi-Annually 2025-02-07 Unit4 Front Bi-Annually 2025-08-04 Goal: Monthly Test Count (Year-Independent)
I want to count the number of tests per month, regardless of the year.
Example:
1. If a test item (e.g., "Unit1") has:
Coverage: Front
Frequency: Quarterly
Most Recent Test Date: 2025/05/25
Then, based on the May 25th date and a quarterly frequency, I want to count this test in the months of February, May, August, and November.
2. If a test item (e.g., "Unit1") has:
Coverage: Back
Frequency: Bi-Annually
Most Recent Test Date: 2025/03/11
Then, based on the Mar 11th date and a Bi-Annully frequency, I want to count this test in the months of Mar and Sep.
Here's an example of the bar chart visualization I'm expecting:
Could you please guide me on how to implement this in Power BI?
I'm encountering difficulties because the corresponding months vary dynamically based on the Frequency.
4 Replies
Hi Lycian
Just checking in on your query regarding the monthly test count based on test frequency and last test date, independent of the year.
As mentioned earlier, BeaBF has shared a detailed response along with a sample Power BI file demonstrating how to dynamically generate the expected monthly distribution based on the frequency type (Quarterly, Bi-Annually, etc.) and most recent test date.
Whenever you get a chance, please take a look at the provided solution and let us know if it meets your expectations or if any part needs further clarification.
Thanks again for reaching out!
Hi Lycian
Just following up on your question about calculating the monthly test count based on test frequency and the last test date, regardless of the year.
As previously mentioned, BeaBF provided a detailed explanation along with a sample Power BI file that demonstrates how to dynamically generate the expected monthly breakdown based on the frequency type (e.g., Quarterly, Bi-Annually) and latest test date.
When you have a moment, please review the shared solution and let us know if it aligns with your expectations or if you need any further clarification.
Appreciate your engagement!
