Summarize table based on dates between begin and end

Question

Guys,&nbsp;I need some help with this formula&nbsp;Productie per dag =SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; GENERATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Planning Civiel',CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES('Date'[Date]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATESBETWEEN('Date'[Date],'Planning Civiel'[begin],'Planning Civiel'[eind]))),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Planning Civiel'[DP gebied],'Date'[Date])&nbsp;Basically i have this sample data and i need to calculate a production per day&nbsp;Area&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Begin&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;End&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Production in m11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 18-08-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24-08-20&nbsp; &nbsp; &nbsp; &nbsp; 6522&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20-08-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25-08-20&nbsp; &nbsp; &nbsp; &nbsp; 5893&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23-08-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31-08-20&nbsp; &nbsp; &nbsp; &nbsp; 8424&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25-08-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03-09-20&nbsp; &nbsp; &nbsp; &nbsp; 7495&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01-09-20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 15-09-20&nbsp; &nbsp; &nbsp; &nbsp; 1.156&nbsp;So basically if the datesbetween&nbsp;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.&nbsp;

amitchandak · Answer

RonaldvdH , You can create a new column 
&nbsp;
Diff =&nbsp; datediff([Begin],[End],Day)
&nbsp;
ratio = divide(sum(Table[ Production in m1]), sum(Table[Diff]))
&nbsp;
Get the dates with days. Check file
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
&nbsp;
Or check the blog. A different way than the file.
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

v-juanli-msft · Answer

Hi&nbsp;RonaldvdH&nbsp;
It seems that amitchandak&nbsp;gives the right answer.
If it is not, please tell us your expected result.
&nbsp;
Best Regards
Maggie

ronaldvdh · Answer

amitchandak&nbsp;thank you for your help&nbsp;In my report your formula doesn't provide any results but luckily not an error too&nbsp;&nbsp;Productie per dag = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Planning Civiel','Date'),'Date'[Date] &gt;= 'Planning Civiel'[begin] &amp;&amp; 'Date'[Date]&lt;= 'Planning Civiel'[eind]),'Planning Civiel'[berekende graaflengte],'Date'[Date]),'Date'[Date]))&nbsp;I think the problem is in the part where you refer to the&nbsp;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&nbsp;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

Forum Discussion

Summarize table based on dates between begin and end

3 Replies

Related content

Merge Summarize Table

Parameter table dimensions - summarize

SUMMARIZE Table

Parameter in summarize

Summarize average monthly table