Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have been searching for the past two days and I'm still stuck. I'm hoping some of you could help me. I have FYWW(Weekly) and FYQ(Quarterly) columns to show exactly how I want on the charts (not sure if this is the way to do that). You can see the example for FYWW below. I converted regular dates to Fiscal years with Fiscal work week. I have separate charts with buttons for weekly, monthly and quarterly. The end-users click the button and show the reports that they want to see.
So, my question is how do I get rolling 8weeks avg from this? What would be the best way to tackle this problem? I will appreciate any input. Thanks!
FYWW | Out | Trash | Yield | Rolling 8 Weeks Avg |
FY19 WW01 | 32 | 14 | 70% | 70% |
FY19 WW36 | 62 | 27 | 70% | 70% |
FY19 WW37 | 34 | 22 | 61% | 67% |
FY19 WW38 | 34 | 54 | 39% | 60% |
FY19 WW39 | 40 | 58 | 41% | 56% |
FY19 WW40 | 29 | 48 | 38% | 53% |
FY19 WW41 | 20 | 58 | 26% | 49% |
FY19 WW42 | 31 | 48 | 39% | 48% |
FY19 WW43 | 49 | 27 | 64% | 47% |
FY19 WW44 | 44 | 22 | 67% | 47% |
FY19 WW45 | 45 | 54 | 45% | 45% |
FY19 WW46 | 55 | 58 | 49% | 46% |
FY19 WW47 | 44 | 48 | 48% | 47% |
FY19 WW48 | 39 | 43 | 48% | 48% |
FY19 WW49 | 50 | 32 | 61% | 53% |
FY19 WW50 | 45 | 24 | 65% | 56% |
FY19 WW51 | 41 | 25 | 62% | 56% |
FY19 WW52 | 47 | 49 | 49% | 53% |
FY20 WW01 | 48 | 51 | 48% | 54% |
FY20 WW02 | 31 | 41 | 43% | 53% |
FY20 WW03 | 21 | 35 | 38% | 52% |
FY20 WW04 | 45 | 24 | 65% | 54% |
Solved! Go to Solution.
The best (well in my view anyway) is to get yourself a dedicated date table with the columns you need ( such as fiscal year and such) on of the columns should be an index so you know where you "are" in the current filter context and how where to go back to. Since you are using fiscal years you cannot use the built in time intelligence. Generic code would look something like:
8 week average= CALCULATE( [AVERAGE MEASURE] FILTER( ALL( DATE TABLE), Date Table[WeekID] <= MAX( Date Table[WeekID]) && DATE TABLE[WeekID] >= MAX( Date Table[WeekID]) -7 ) )
The best (well in my view anyway) is to get yourself a dedicated date table with the columns you need ( such as fiscal year and such) on of the columns should be an index so you know where you "are" in the current filter context and how where to go back to. Since you are using fiscal years you cannot use the built in time intelligence. Generic code would look something like:
8 week average= CALCULATE( [AVERAGE MEASURE] FILTER( ALL( DATE TABLE), Date Table[WeekID] <= MAX( Date Table[WeekID]) && DATE TABLE[WeekID] >= MAX( Date Table[WeekID]) -7 ) )
@Anonymous
Thank you for pointing me to the right direction. However, your code works as long as the WeekID doesn't go back to 1 or last few weeks where the data is ending. I'm using fiscal year so at the beginning of July, WeekID goes back to 1. All my Week 1 and up are wrong. How can I fix it?
Updates: never mind I figured it. I used Dates instead of Weeks. It's working now. Thanks!
cool, glad you got it to work. You want a running count of weeks going back, not one that restarts. This can be done pretty easily in Power Query or a calculated column in your date table, but looks like you are all set.
I will try this method. Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |