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

[Anonymous]

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

19m ago

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.

Category	Segment	Brand	Size Range	Vendor	PPG	Measures	5/28/2021	6/4/2021	6/11/2021	6/18/2021	6/25/2021	7/2/2021	7/9/2021	7/16/2021	7/23/2021	7/30/2021	8/6/2021	8/13/2021	8/20/2021	8/27/2021	9/3/2021	9/10/2021
Goat Food	Dry Food	Brand 1	0-2.5LB	Vendor 1	Dry 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,642	50005
Goat Food	Dry Food	Brand 1	0-2.5LB	Vendor 1	Dry Extra Small bag 12 oz	Unit Sales	39,213	40,561	37,918	38,363	39,529	41,438	38,002	38,919	37,676	37,609	37,101	36,443	37,110	36,913	42,051	47288
Goat Food	Dry Food	Brand 1	10-16LB	Vendor 1	Dry 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,050	536748
Goat Food	Dry Food	Brand 1	10-16LB	Vendor 1	Dry Bonus Bag 15 oz	Unit Sales	56,574	60,334	59,481	57,316	55,420	63,401	55,000	54,329	54,874	56,096	58,257	54,715	54,295	53,265	57,452	54719

[Anonymous]

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.

Category	Segment	Brand	Size Range	Vendor	PPG	Measures	5/28/2021	6/4/2021	6/11/2021	6/18/2021	6/25/2021	7/2/2021	7/9/2021	7/16/2021	7/23/2021	7/30/2021	8/6/2021	8/13/2021	8/20/2021	8/27/2021	9/3/2021	9/10/2021
Goat Food	Dry Food	Brand 1	0-2.5LB	Vendor 1	Dry 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,642	50005
Goat Food	Dry Food	Brand 1	0-2.5LB	Vendor 1	Dry Extra Small bag 12 oz	Unit Sales	39,213	40,561	37,918	38,363	39,529	41,438	38,002	38,919	37,676	37,609	37,101	36,443	37,110	36,913	42,051	47288
Goat Food	Dry Food	Brand 1	10-16LB	Vendor 1	Dry 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,050	536748
Goat Food	Dry Food	Brand 1	10-16LB	Vendor 1	Dry Bonus Bag 15 oz	Unit Sales	56,574	60,334	59,481	57,316	55,420	63,401	55,000	54,329	54,874	56,096	58,257	54,715	54,295	53,265	57,452	54719

[v-shex-msft · Accepted solution]

Hi @Anonymous,

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

[lbendlin]

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

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

etc.