cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hsm0507 Frequent Visitor
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
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.
hsm0507 Frequent Visitor
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 CodeCurrent 12 Week Ships % Next Day
100876.92%
108125.00%
109161.54%
114586.46%
358985.80%
3594100.00%
3600100.00%
367792.04%
796095.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
Community Support Team

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

Hi @hsm0507,

 

Can you share your file if that's possible? You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

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. 

Need-to-calculate-a-12-week-measure-percent-for-a-weekly-trend-line

 

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.