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