Rolling 12m average Win Rate

[Harun072]

Hi Experts,

I'm kindly asking for your help. I'm trying to create a rolling average for win rate, and while calculating the win rate itself is straightforward, I haven’t been able to figure out how to calculate a rolling average for it.

Ideally, I’d like to create two DAX measures:

1. A 12-month rolling average of win rate that includes all months, even if there’s no data (blank months should still be shown with their contribution to the average as blank or zero).

2. A 12-month rolling average of win rate that excludes months where data is blank (i.e., months without any won/lost opportunities), and those months should be hidden from the table via modified filter context.

I can provide more context if needed. For reference, my Date table is properly connected to the dataset and is marked as a date table in the model.

Thanks a lot in advance for any help!

[v-venuppu]

Hi @Harun072 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @Harun072 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @Harun072 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @Harun072 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Jihwan_Kim for the prompt response.

Building on the PBIX shared by Jihwan_Kim, here’s a breakdown of your follow-up questions:

1.How can I check which months have been captured for a specific period like April 2024?

The WINDOW function used in the "Rolling 12 months win rate measure" dynamically looks back 11 months from the current row, based on the order defined by 'calendar'[Year -Month sort]. So for April 2024, it includes data from May 2023 to April 2024, sorted by that column.

To visually debug this, you could create a table showing:

• 'calendar'[Year -Month]
• [Win rate %]
• [Rolling 12 months win rate]

This will help confirm the rolling window visually for each row.

2.What happens with months where there’s no data, like May to August 2024? (What happens with blank months?)

• In the first measure (Rolling 12 months win rate), blank months are still part of the 12-month window. If those months have no win/lost data, they contribute nothing to the average (resulting in a diluted percentage).
• In the second measure (Rolling 12 months win rate hide blank), the rolling window is still fixed at 12 months, but the measure returns BLANK() when the current month has no data (both win/lost counts are blank). So that month is effectively skipped in the chart.

3.How can I hide future months that haven’t started yet?

You can use a calculated column in the 'calendar' table:

IsFutureMonth = IF('calendar'[Date] > TODAY(), 1, 0)

• Then use this column as a filter on your visual (e.g., only show rows where IsFutureMonth = 0). This will automatically hide future months.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Lost Count: = 
SUM( data[lost_count] )

Win Count: = 
SUM( data[win_count] )

Win rate %: = 
VAR _win = [Win Count:]
VAR _lost = [Lost Count:]
RETURN
    DIVIDE ( _win, _win + _lost )

WINDOW function (DAX) - DAX | Microsoft Learn

Rolling 12 months win rate: = 
CALCULATE (
    [Win rate %:],
    WINDOW (
        -11,
        REL,
        0,
        REL,
        ALL (
            'calendar'[Year],
            'calendar'[Month name],
            'calendar'[Month number],
            'calendar'[Year -Month sort]
        ),
        ORDERBY ( 'calendar'[Year -Month sort], ASC )
    )
)

Rolling 12 months win rate hide blank: = 
IF (
    NOT (
        [Win Count:] = BLANK ()
            && [Lost Count:] = BLANK ()
    ),
    CALCULATE (
        [Win rate %:],
        WINDOW (
            -11,
            REL,
            0,
            REL,
            ALL (
                'calendar'[Year],
                'calendar'[Month name],
                'calendar'[Month number],
                'calendar'[Year -Month sort]
            ),
            ORDERBY ( 'calendar'[Year -Month sort], ASC )
        )
    )
)

[Harun072]

Thank you so much! I still have a few more questions as I'm trying to fully understand this and adapt it to my model and analysis:

• How can I check which months have been captured for a specific period like April 2024?
  I want to make sure that the win rate calculation for a given month includes the correct data. What’s the best way to confirm that the right opportunities are being considered in that month?

• What happens with months where there’s no data, like May to August 2024?
  If there are blank months with no opportunities, does the measure still try to calculate something? How does it handle those gaps?

• 

• How can I hide future months that haven’t started yet?
  Right now, it looks like the visuals go beyond the current date—for example, from May 2025 all the way to April 2026. How do I stop it from showing those future months where we don’t expect any data yet?