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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors