cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Sum by date with Start date and End date

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 Smiley Happy


Steve

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Sum by date with Start date and End date

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!

Super User
Super User

Re: Sum by date with Start date and End date

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




2 REPLIES 2

Re: Sum by date with Start date and End date

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!

Super User
Super User

Re: Sum by date with Start date and End date

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries