Forum Discussion
Rolling 12m average Win Rate
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.
Super UserHi,
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 )
)
)
)
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?