Summarize table based on dates between begin and end

[RonaldvdH]

Guys,

I need some help with this formula

Productie per dag =
SUMMARIZE(
        GENERATE(
               'Planning Civiel',CALCULATETABLE(
                VALUES('Date'[Date]),
                      DATESBETWEEN('Date'[Date],'Planning Civiel'[begin],'Planning Civiel'[eind]))),
                'Planning Civiel'[DP gebied],'Date'[Date])

Basically i have this sample data and i need to calculate a production per day

Area                   Begin                   End                 Production in m1

1                        18-08-20              24-08-20        652

2                        20-08-20              25-08-20        589

3                        23-08-20              31-08-20        842

4                        25-08-20              03-09-20        749

5                        01-09-20              15-09-20        1.156

So basically if the datesbetween Begin and End is 8 days and the production is 1600 m1 it should summarize to 8 dates and 200 m1 per day, i hope you get my point.

[v-juanli-msft]

Hi @RonaldvdH 

It seems that amitchandak gives the right answer.

If it is not, please tell us your expected result.

Best Regards

Maggie

[amitchandak]

@RonaldvdH , You can create a new column

Diff =  datediff([Begin],[End],Day)

ratio = divide(sum(Table[ Production in m1]), sum(Table[Diff]))

Get the dates with days. Check file

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Or check the blog. A different way than the file.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[RonaldvdH]

@amitchandak thank you for your help

In my report your formula doesn't provide any results but luckily not an error too 

Productie per dag = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Planning Civiel','Date'),'Date'[Date] >= 'Planning Civiel'[begin] && 'Date'[Date]<= 'Planning Civiel'[eind]),'Planning Civiel'[berekende graaflengte],'Date'[Date]),'Date'[Date]))

I think the problem is in the part where you refer to the Sickness[id] where i need to refer to the meters for production.

Ive made an example in excel as you can see here, this is basically what i want powerbi to do

I need to create a table with the dates based on MIN/MAX values in table 'Planning Civiel' and then it needs to calculate the amount of production (per workday) in meters.

In the excelformula you can see that on the 24th of augustus we are working in 4 different areas so it calculates based on the average. I hope this clarifies it a bit more