cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Need to calculate a 12 week measure (percent) for a weekly trend line

Hi, I have created a measure (% next day business) based on most recent 12 weeks.  I need to now capture and show that percentage on a weekly basis. Is this possible being that it is a percentage?  Here is the formula for the measure:

% Next Day Shipments = if([Total Shipments (12 Weeks)], [Next Day Shipments]/[Total Shipments (12 Weeks)],0)

Corresponding formulas:
Next Day Shipments = CALCULATE(COUNTA('Query2'[Next Day ProNumber]),
FILTER('report DateKey', 'report DateKey'[Fiscal Year Week] <= MAX('report DateKey'[Fiscal Year Week])-1 && 'report DateKey'[Fiscal Year Week] >= MAX('report DateKey'[Fiscal Year Week])-12))

Total Shipments (12 Weeks) = CALCULATE(DISTINCTCOUNT('Query2'[Pro Number]),
FILTER('report DateKey', 'report DateKey'[Fiscal Year Week] <= MAX('report DateKey'[Fiscal Year Week])-1 && 'report DateKey'[Fiscal Year Week] >= MAX('report DateKey'[Fiscal Year Week])-12))

I have seen several posts about creating a rolling average but all seem to be in relation to sales, etc.  Not when starting with a percentage.

Thanks for any thoughts!
3 REPLIES 3
Community Support Team

## Re: Need to calculate a 12 week measure (percent) for a weekly trend line

Hi @hsm0507,

Can you share a sample, please?

What is the result exactly? You already have the formulas. You can add the measure in the Y-axis and the Weeks in the X-axis. You can get the result finally.

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Need to calculate a 12 week measure (percent) for a weekly trend line

Hi Dale, my base data table is at an item number level and contains item number, account, order date and number of days it takes for a shipment to go from point A to B.  The aggregated data is like this:

 Account Code Current 12 Week Ships % Next Day 1008 76.92% 1081 25.00% 1091 61.54% 1145 86.46% 3589 85.80% 3594 100.00% 3600 100.00% 3677 92.04% 7960 95.01%

These percents are based on the most recent 12 weeks.  What I am trying to achieve is to calculate these percents again each week for the past 12 weeks to display on a trend line. Currently if I add it to a line graph, it results in a straight line.  Apologies, I am not sure how to copy the graph here.

Community Support Team

## Re: Need to calculate a 12 week measure (percent) for a weekly trend line

Hi @hsm0507,

Your data should be like this.

Account Code                Week                          Current 12 Week Ships % Next Day

 1008 2018-31 0.75 1008 2018-32 0.82 1008 2018-33 0.95 1008 2018-34 0.26 1008 2018-35 0.36 1008 2018-36 0.85 1008 2018-37 0.95 1008 2018-38 0.56 1008 2018-39 0.64 1081

The visual should be like this.

Isn't it?

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.