topic Re: Total No of Days Spent month wise breakup using Time periods in DAX Commands and Tips

Total No of Days Spent month wise breakup using Time periods

Anonymous — Thu, 30 Sep 2021 04:54:13 GMT

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

Re: Total No of Days Spent month wise breakup using Time periods

amitchandak — Thu, 30 Sep 2021 05:15:18 GMT

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)

Re: Total No of Days Spent month wise breakup using Time periods

Anonymous — Thu, 30 Sep 2021 06:53:41 GMT

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

Re: Total No of Days Spent month wise breakup using Time periods

Anonymous — Thu, 30 Sep 2021 07:57:38 GMT

Please ignore the 2nd Image and consider this