Rolling Percentage On Time

[LEC]

Hi. I know some posts have addressed the issue, but I still can't get it to work for my specific example.

In Excel, I have data by month, destination, and carrier, a number of deliveries, and number of late deliveries. I then calculate an on-time percentage for each month, and also a rolling on time percentage.

I have uploaded my information into Power BI and have a measure to calculate on time percentage for each month ((total deliveries-total late)/total deliveries). This is great to see what is going on each month, but I need a more smooth curve over time.

So lets say I have the following (third column being a calculated measure):

Jan     1 late     5 deliveries     80% on time

Feb    0 late      3 deliveries    100% on time

Mar    2 late     6 deliveries    33% on time

April   0 late     4 deliveries    100% on time

I could use filters on this graph and get the info by carrier, destination, etc. This is great.

However, I need the exact same thing with rolling data, able to be filtered.

So for the above example, I would want to show:

Jan  80% on time

Feb  88% on time

Mar  79% on time

Apr   83% on time

Any thoughts?

[v-sihou-msft]

@LEC

In this scenario, I suggest you add a month number in your table for cumulative calculation. You can create following measures to calculate running total late and delivery.

running total late = CALCULATE(SUM('Table'[Late]),FILTER(ALL('Table'),'Table'[Month Number]<=MAX('Table'[Month Number])))

running total delivery = CALCULATE(SUM('Table'[Delivery]),FILTER(ALL('Table'),'Table'[Month Number]<=MAX('Table'[Month Number])))

Then calculate the percentage:

Pct = 1 - 
CALCULATE(SUM('Table'[Late]),FILTER(ALL('Table'),'Table'[Month Number]<=MAX('Table'[Month Number])))
/
CALCULATE(SUM('Table'[Delivery]),FILTER(ALL('Table'),'Table'[Month Number]<=MAX('Table'[Month Number])))

Regards,

[LEC]

@v-sihou-msft

This is great! Thank you!

This is basically what I was trying and could not get it to to work.

One issue is that it does not link to other characteristics such as the destination or carrier for slicing purposes. How do I make that happen?