Can you provide sample data or sample workbook?
It's difficult to provide the answer without understanding your data structure.
Could attach sample data as twbx format.
Really easy one works.
I'm not sure if this will be feasible for your datasource type or data structure,
but maybe it can give an idea for one method of approach.
It uses the Cross Join method described here:
The data is joined to a lookup table which is just a list of the first day of the month
covering the entire time period of your data.
This may not be feasible for the size of your dataset, but next
there is join on the calculated field of 1, so all rows are joined together.
Next there is a filter to just the pertinent lookup dates:
[Start Date (Months)]<=[Lookup Month (Months)]
[End Date (Months)]>=[Lookup Month (Months)]
Lastly, there is a day counter:
IF [Start Date (Months)]=[Lookup Month (Months)]
THEN DATEDIFF('day',[Start Date],DATETRUNC('month',DATEADD('month',1,[Lookup Month (Months)])))
ELSEIF [End Date (Months)]=[Lookup Month (Months)]
THEN DATEDIFF('day',[lookupDate],DATEADD('day',1,[End Date]))
Please see workbook and spreadsheet attached in the Forum thread.
Thanks for the sample workbook. please find my solution attached.
Below is the steps
1st, join your data to a calendar table by key 1=1
2nd, create a filter to filter the data
and the result
Hope this helps
Datedifftest_v10.2.twbx 29.5 KB
This works only if your start => end is only 1 month off at maximum.
( more than 1 months case, need to join with another calendar table)