Forum Discussion
Summarizing Values At Weekly Intervals
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!
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
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.
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!
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.