CODEWITHSUNDEEP
daxdata-modelingpowerbi-desktop
Paul
Paul

Reputation: 25

Filter data with start & end date in intervals (months, weeks)

I am trying to build a data-analysis table (in PowerBi if that matters) that shows sum of task hours per resource (row) and date-window (column). I.e getting a result as ...

Resource Month 1 Month 2
AB 40h 30h
BB 20h 10h

My data however is structured in a way that I have one data point per resource/task combination without breaking down the date. I.e. the data is structured like ...

Resource Task Hours Start End
AB XX 10h 10.10.22 01.02.23
AB XZ 5h 01.11.22 05.11.22

So i need to sum all tasks per resource but also break them down to how many hours per month. Ideally i can also switch to weeks view in my dashboard.

How can I best achieve this? Transform the data? Some special filter?

Any Tips or pointers to tutorials ecc. would be great. Thanks.

Best

Upvotes: 1

Views: 349

Answers (1)

Darkitechtor
Darkitechtor

Reputation: 349

If you can store a lot of data, you should do something like this:

  1. calculate hours per date for every Resource-Task group (this can be done inside original table);
  2. create a new calendar table (one column with dates) and cross-join it with distinct Resources;
  3. add a column to newly created table where you will calculate sum of hours per this date and Resource;
  4. use this analytical table for your purposes grouping data by necessary periods.

Upvotes: 1

Related Questions