Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 sam...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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/355322#M160013

 

Thanks for your help!