cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tjco225
Regular Visitor

Need help with Creating metrics/measures for Last 4 weeks, prior 4 weeks etc.

Struggling to create measures/columns with time data or latest x period data. Any help is appreciate

Hello There,

 

I am trying to create a way to show $ Sales Post and Pre a certain date, that date being 7/09/21. All my dates are at the weekly level with none missing. 

 

I want to create dynamic date measures that will compare the latest 4 weeks, 12 weeks and 24 weeks to these static measure values. Example- Latest 4 weeks Sales, vs Static 4 weeks sales prior to 7/09/21.

 

So when I get $ Sales data for week ending 9/17/21. I want to be able to click refresh and I can see the latest 4 weeks of $ sales data sum'd vs the L4 $ Sales prior to 7/09. 

 

Is there a way I can acheive this? I would like to be able to do this and click a button/filter etc and be able to look at all my data at the last 12 weeks or Last 4 weeks or Last 24 Weeks intervals.

 

Another way to maybe achieve this would just be used "Top N" or "Bottom N", and using top 4 or bottom 4 for the last 4 weeks etc. I'm just not good enough with BI yet to figure it out. 

 

I greatly appreciate any and all help! 

 

Please see below for drop box links to data set and BI Report.

 

https://www.dropbox.com/scl/fi/di368z0slg29sx92osmq4/BI-Example-Excel-Data-Set.xlsx?dl=0&rlkey=7mt9l...

 

https://www.dropbox.com/s/hzhjjxjp59e7ymo/BI%20Example.pbix?dl=0

 

I have posted the randomized data from excel below as well.

 

CategorySegmentBrandSize RangeVendorPPGMeasures5/28/20216/4/20216/11/20216/18/20216/25/20217/2/20217/9/20217/16/20217/23/20217/30/20218/6/20218/13/20218/20/20218/27/20219/3/20219/10/2021
Goat FoodDry FoodBrand 10-2.5LBVendor 1Dry Extra Small bag 12 oz$ Sales$39,005$40,349$37,637$38,126$39,321$41,222$39,256$41,256$40,087$39,893$39,301$38,612$39,327$39,228$44,64250005
Goat FoodDry FoodBrand 10-2.5LBVendor 1Dry Extra Small bag 12 ozUnit Sales39,21340,56137,91838,36339,52941,43838,00238,91937,67637,60937,10136,44337,11036,91342,05147288
Goat FoodDry FoodBrand 110-16LBVendor 1Dry Bonus Bag 15 oz$ Sales$477,871$508,486$499,788$504,175$492,738$536,962$510,800$533,625$542,676$551,259$573,158$537,889$533,619$526,974$565,050536748
Goat FoodDry FoodBrand 110-16LBVendor 1Dry Bonus Bag 15 ozUnit Sales56,57460,33459,48157,31655,42063,40155,00054,32954,87456,09658,25754,71554,29553,26557,45254719

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @tjco225,

You can use weeknum function to extract the current week number from your date field, then you can use it with the year part of the current date as the filter condition for calculations. They should simply get last n week aggerations results.

BTW, you may need to add some condition checking if the current week number is less than 4. They may need to do some year conversions.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @tjco225,

You can use weeknum function to extract the current week number from your date field, then you can use it with the year part of the current date as the filter condition for calculations. They should simply get last n week aggerations results.

BTW, you may need to add some condition checking if the current week number is less than 4. They may need to do some year conversions.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

lbendlin
Super User
Super User

For "last four weeks"  use DATEADD(...,-28,DAY)

 

for "four weeks before that use DATESBETWEEN(DATEADD(...,-56,DAY),DATEADD(...,-28,DAY))

 

etc.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.