I have spent 2 days trying to follow what some of the other posts on similar datasets have suggested as a solution but i'm having a hard time grasping how to do replicate it for my dataset. (I just bought a book and planning to take a class to grow in these skills fundimentaly, but until then I would greatly appreciate any help or direction).
I need to create a report that shows me the last 5 weeks average sales $ amount won for that week.
Week 1; Last 5 weeks average
Week 2: Last 5 weeks average
Week 3: Last 5 weeks average
I need this to be in a table so that I can manually add (or via calculation) what the goal is for that week.
Here is what i've tried so far:
JV Sum = SUM(Jobs_Invoices_Merged[Job Value])
5 Week Moving Sum =
LASTDATE(Jobs_Invoices_Merged[Job Created]),-35, DAY
Exported this from PBI to excel and stuck it on a OneDrive..
Source Table: Jobs_Invoices_Merged
Date Table: Sales_Avg_Date_Table
These tables are not yet linked because I'm not sure how to link them properly (might be where my issue is)..
I also created a filter to show only days bettween "completed & scheduled" to be between 0-30 days age, but I'm assuming that i can add that into the "visual level filters".. If not, then I have this custom column to use as a calculation if needed:
These are my attempted results:
I'm realizing that I have much more to learn since we've been getting into these visuals that require more complex nested dax formulas..
Thank you for helping me get this set up. This showed me how to setup the calendar and the measure for calculating on a time scale, which is the first part of the issue I was having.
Could you also help me figure out how to create a rolling 5 week "average" as well? Where would I need to insert the "AVERAGE()" Formula, or would I need to divide the sum by 35 somewhere? I'll try to figure it out on my own with what you've given me but if you respond to how to do this, it would be greatly helpful as well.
Thank you @v-ljerr-msft! That works! The data looked wrong because it was calculating the average based on days instead of weeks, so to solve this I needed to create a seperate table based on weeks and aggregate the data so show the sum for that week. Im just going to adjust the formula to match the new table but the main structure is the same.