[Help] Creating Monthly Test frequency visualization (Year-Independent)

[Lycian]

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.

[BeaBF · Accepted solution]

@Lycian Hi! Attack you can find the pbix with the solution. The output:

Hope it works! if you need to fix something, please send me details!

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[v-aatheeque]

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!

[v-aatheeque]

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!

[v-aatheeque]

Hi @Lycian 

Just following up regarding your query on calculating Monthly Test Counts (year-independent) for your test items dataset, where each item includes:

• Coverage types (Front / Back),

• Test frequencies (Quarterly, Bi-Annually, Annually, etc.),

• And Test Dates with expected repeating patterns across the calendar year.

As per your requirement identifying the recurring test months based on the most recent test date and frequency, @BeaBF has already reviewed the sample data you shared and provided:

• A customized solution
• A sample PBIX file with your logic implemented
• Output aligned with your expectations (e.g., Quarterly & Feb, May, Aug, Nov)

If we don’t hear back, we may close this thread in line with our community guidelines, but you’re always welcome to post a new query anytime.
Thank you for being part of the Microsoft Fabric Community!

Thanks for your attention, and looking forward to hearing from you!