Sum by date with Start date and End date

[SteveCampbell]

Hello,

I have a table which looks like this:

Start              End              Emploee           Allocation

1/1/16         1/20/16              A                      0.5

1/10/16       2/20/16              B                      0.8

1/15/16       2/15/16             A                       0.2

What I'm trying to do is show a weekly/daily sum of "Allocation" that can be filtered by "Employee". I am creating a daily date table, but am stuck on how to join it. "Employee" may change and have values added.

I've explored a few ideas but am not that confident in DAX.

Thanks in advance for your help 🙂

Steve

[SteveCampbell · Accepted solution]

So I figured one way,  probably not the easiest but:

In a blank query:

set three variables:

start = found first start date

end = last end date

DistinctTable = table with two columns: distinct employee names, the value 1

created a date table with every day between start and end date

added custom column with value of 1

Joined to my variable table DistinctTable on the two columns that equal 1 (returning all employee names as a table on each date)

Expanded column on employee name to give a row for each date and each employee

Then on that table, I calulated column to sum allocation if that date / employee falls correctly

There is probably a better way and would appreciate anyones input!

[SteveCampbell · Accepted solution]

So I figured one way,  probably not the easiest but:

In a blank query:

set three variables:

start = found first start date

end = last end date

DistinctTable = table with two columns: distinct employee names, the value 1

created a date table with every day between start and end date

added custom column with value of 1

Joined to my variable table DistinctTable on the two columns that equal 1 (returning all employee names as a table on each date)

Expanded column on employee name to give a row for each date and each employee

Then on that table, I calulated column to sum allocation if that date / employee falls correctly

There is probably a better way and would appreciate anyones input!

[ImkeF · Accepted solution]

That's a fine solution as long as the performance is OK for you.

For larger datasets you might prefer a DAX-solution: http://community.powerbi.com/t5/Desktop/Count-of-Active-Contracts-by-Start-en-End-date/m-p/94279#M39...