Forum Discussion
Summarizing Values At Weekly Intervals
I'm looking to build an area graph that summarizes values at weekly intervals using 3 varablies: Start Date, End Date, and Rate (hrs/week). What is the best way to achieve this?
Below is some sample data.
| Project No. | Start Date | End Date | Rate [hrs/week] |
| 19001 | 4/1/2019 | 6/2/2020 | 40 |
| 19002 | 1/1/2019 | 4/9/2021 | 30 |
| 19003 | 3/20/2019 | 4/25/2019 | 20 |
| 19004 | 6/7/2019 | 4/7/2020 | 25 |
| 19005 | 10/1/2019 | 12/1/2021 | 62 |
I was able to accomplish this using CrossJoin and Filter between my excel-based datasouce and Cmcmahan's suggestion to create a date dimension for weeks (link copied again below) .
1. Create a Date Dimension:
- https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
- Note: I change the duration from 1 to 7 for weeks
2. Link Datasources with CrossJoin and Filter:
Thanks for your help!
4 Replies
The best way to do this is to create a date dimension. Once you add a week number field to the date dimension, you can group data by that field.
You can read more about date dimensions here: https://radacad.com/do-you-need-a-date-dimension
And you can use this example to create your own: https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columnsI created the date dimension for weeks (see screenshot below). What is the best approach to calculate "When an activity falls within a particular range (StartofWeek/EndofWeek), summarize the Rate (hrs/week)?" Do I need to define a relationship between queries? Or is there another function that might accomplish this in a better way?
My apologies if this is a basic question. I've spent too long in Excel and am new to DAX.
I was able to accomplish this using CrossJoin and Filter between my excel-based datasouce and Cmcmahan's suggestion to create a date dimension for weeks (link copied again below) .
1. Create a Date Dimension:
- https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
- Note: I change the duration from 1 to 7 for weeks
2. Link Datasources with CrossJoin and Filter:
Thanks for your help!
