Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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.

 

 

Hi @Anonymous,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.