Summarizing Values At Weekly Intervals

[Anonymous]

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

[Cmcmahan]

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-columns

[Anonymous]

I 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.

[Cmcmahan]

While it is technically possible to group, filter, and summarize data with just the columns you have (start date & end date) and the "week dimension" you created, the expressions get complicated very quickly and are prone to failure.

I would suggest going more detailed with your date dimension.  Date dimensions work much better when they list out every possible date.  I would just add all the columns suggested in the 2nd link from my previous reply, but if you're dead set on having a minimalist date dimension, it should contain two columns at a minimum: Date & Week of Year. You can add other info (like StartOfWeekDate/EndOfWeekDate, or all the other fields suggested) if you like, but these two are the keys to making this solution work easily.

From there you can use a measure like this to determine the sum for a given timeframe:

WeekTotal = 
SUMX(
    'Project Info', 
    IF( [Start Date]<=MAX(dimDate[Date]) && [End Date] >= MIN(dimDate[Date]), [Rate] )
)

Then you drop it into a visual, along with Year and Week of Year, and you can get a table that looks like this:

I created [Beginning of Week] as a measure in order to give faster context to each row, but the year/week of year combination is doing the context changing work.

[Anonymous · Accepted solution]

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:

https://community.powerbi.com/t5/Desktop/DAX-with-calculation-between-start-and-finish-dates/m-p/355...

Thanks for your help!