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.
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/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 |
Solved! Go to 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
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
For "last four weeks" use DATEADD(...,-28,DAY)
for "four weeks before that use DATESBETWEEN(DATEADD(...,-56,DAY),DATEADD(...,-28,DAY))
etc.
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |