Total No of Days Spent month wise breakup using Time periods

[Anonymous]

Hi All,

I'm a newbee. I have a situation where i need to calculate the total no. of days spent on each task on monthly basis.
I have time periods in a slicer with values "Current Month", "Prev Month", "Last 3 Months", "Next Month" & Next 3 Months"

Based on the time period selected, i want to show the total no of days spent on a task.

Note: My Date Table is built on Start Date.

It will be really helpful with a sample code snippet or any link.

Thanks in Advance

Srinivas K

[Anonymous]

Hi @amitchandak 
Thanks alot for your response. But if you please go thru the post again, you will come to know my exact need.
if u see, task2 in the screenshot, it starts on  Sep-03-201 & ends on Oct -03 where total days will be 30 days. but, When i select time period as Current Month, it should calculate total days from start date to month end of current month and the remaining days (2Days)should be covered under next month i.e. Oct. 

Thanks ,

Srinivas K

[amitchandak]

@Anonymous , Try measures like

This Month=
var _min1 = eomonth(today(),-1)+1
var _max1 = eomonth(today(),0)
var _min = if(min([Task Start Date]) >= _min1, min([Start Date]) , _min1)
var _min = if(Max([Task End Date]) <= _max1, Max([Task End Date]) , _max1)
return
datediff(_min,_max, Day)

Last Month=
var _min1 = eomonth(today(),-2)+1
var _max1 = eomonth(today(),-1)
var _min = if(min([Task Start Date]) >= _min1, min([Start Date]) , _min1)
var _min = if(Max([Task End Date]) <= _max1, Max([Task End Date]) , _max1)
return
datediff(_min,_max, Day)

Last 3 Month= // Use -4 , -1 in place of -3 and 0 if needed
var _min1 = eomonth(today(),-3)+1
var _max1 = eomonth(today(),0)
var _min = if(min([Task Start Date]) >= _min1, min([Start Date]) , _min1)
var _min = if(Max([Task End Date]) <= _max1, Max([Task End Date]) , _max1)
return
datediff(_min,_max, Day)

[Anonymous]

Please ignore the 2nd Image and consider this