Struggling with ADDCOLUMNS and limiting date range of calcuation

[masplin]

I have a table of clients and each client has a starting date.  i want to calculate the number of billable hours for each client in the first 7 days of their activity. 

I was pretty sure this should work

CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(Client,
                                                              Client[Account Number],
                                                               Client[Client Start Date]),
                                         "Week1Hour",CALCULATE([Billable Hours],FILTER(DateTable,DateTable[Date]<=Client[Client Start Date]+6 ))
                                                  ), USERELATIONSHIP(DateTable[Date],Client[Client Start Date]))

 by adding a column to a table of just client numbers then I can do a SUMX or AVERAGEX over this table.  The problem is the Week1Hour is coming out as the total billable hours so the  filter on the datetable doesn't seem to be doing anything.   I have no idea why this sodens't work as looks logical to me?

Any advice appreciated

Mike

[v-eqin-msft]

Hi @masplin ,

Can you please share more detail information to help us clarify your scenario?

Refer to:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[amitchandak]

@masplin , Try like

sumx(ADDCOLUMNS(SUMMARIZE(Client,
Client[Account Number],
Client[Client Start Date]),
"Week1Hour",CALCULATE([Billable Hours],FILTER(DateTable,DateTable[Date]<=Client[Client Start Date]+6 )
, USERELATIONSHIP(DateTable[Date],Client[Client Start Date])) ), [Week1Hour])

[masplin]

The issue is the Week1Hour calculation is generating the wrong number . I think you just wrapped it in a SUMX 

Below is the table being generated.  406913 actually only did 10.5 hours in the first 7 days, but did 21 hours in total. so the date filter is not actually being applied. That's my issue

[amitchandak]

@masplin ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[masplin]

Its a huge model need to create somethnig similar in a test model.  Got called for jury service which is slowing me down!

[masplin]

Soprry delay. Now i'm really confused

https://www.dropbox.com/sh/lgvgpnb2cnl527f/AADbTe97fyeBmFgGUXYAB6G_a?dl=0 

I created a super simple version with just one client and a few transactions.  does the calcuation exactly as you expect just adding up the billable hours in first 7 days

Measure is almost exactly the same as my main model with active relationship from booking date to date table and inactive relationship client start date to datetable. 

Change my main model to use SUM(Booking Billable Duration Corrected]) made no difference.  Took off every page/model filter. 

One difference is my main modle is putting the wrong number al lin the first date 4/10

I'm really stuck on this as clearly something else in my model is interacting with this calculation. Really irritating!!!! 

I think I have managed to shrink my actual model down here for comparison

Real model 

Any clues appreciated

Mike

New Client Week 1 Hours = SUMX(CALCULATETABLE(ADDCOLUMNS(SUMMARIZE(Client,
                                                              Client[Account Number],
                                                               Client[Client Start Date]),
                                         "Week1Hour",CALCULATE(SUM(Bookings[Billable Hours]),FILTER(DateTable,DateTable[Date]<=Client[Client Start Date]+6 && DateTable[Date]>=Client[Client Start Date]))
                                                  ), USERELATIONSHIP(DateTable[Date],Client[Client Start Date])),
                                          [Week1Hour])