cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Rolling 12 months %

Hello,

This is what I need (Ideally with the rolling showing accross a longer period but this is just a sample)-

 YYYYMM under 1hr Total In month % Rolling 12 % 012022 456 500 91.20% 022022 678 700 96.86% 032022 480 520 92.31% 042022 562 578 97.23% 052022 574 600 95.67% 062022 586 600 97.67% 072022 598 620 96.45% 082022 610 630 96.83% 092022 622 640 97.19% 102022 634 645 98.29% 112022 646 650 99.38% 122022 658 670 98.21% 96.61%

This is what I have -

Calculate([Total],DATESINPERIOD(Calendar_Powerquery[Date-clean],MAX(Calendar_Powerquery[Date-clean]),-12,MONTH))
I also have the same measure for Incidents under 1 hr.
The issue I get when I put the data into a table in Power Bi is I can get the in month figure but I need a rolling which is worked out by summing the last 12 months incidents under 1 hr and the totals and then dividing.
1 ACCEPTED SOLUTION
Super User

Try this measure. The tables have a relationship on the date column. The visual uses DimDate[YYYYMM].

``````Rolling 12 Month % =
VAR vUnder1Hour =
CALCULATE (
SUM ( FactTable[under 1hr] ),
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
VAR vTotal =
CALCULATE (
SUM ( FactTable[Total] ),
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
VAR vResult =
DIVIDE ( vUnder1Hour, vTotal )
RETURN
vResult``````

Proud to be a Super User!

2 REPLIES 2
Resolver I

Thanks @DataInsights

Super User

Try this measure. The tables have a relationship on the date column. The visual uses DimDate[YYYYMM].

``````Rolling 12 Month % =
VAR vUnder1Hour =
CALCULATE (
SUM ( FactTable[under 1hr] ),
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
VAR vTotal =
CALCULATE (
SUM ( FactTable[Total] ),
DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
)
VAR vResult =
DIVIDE ( vUnder1Hour, vTotal )
RETURN
vResult``````

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors