1 Reply Latest reply on Nov 29, 2018 8:20 AM by Cassandra Spurgeon

# How to calculate total minutes by month utilizing parameters and FIXED in calculation

I'm working on a calculation to utilize as a denominator in other various equations, but am having difficulties when a user selects dates that fall into more than a single month.

Currently my calculation total's up the entire time a user has selected, regardless of month:

//Number of Days Selected (+1 to account for the first day selected)

(DATEDIFF('day',MIN([Start Date]),MIN([End Date]))+ 1)

*

//Number of Minutes Selected

DATEDIFF('minute',MIN(DATETIME('1/1/2018 ' + [Parameters].[Start Time])),MIN(DATETIME('1/1/2018 ' + [Parameters].[End Time])))

*

//Number of Rooms that are selected after all filters (that were added to context) are applied

SUM({FIXED : COUNTD([RoomName]) })

If a user selects any amount of days in a single month, it works as expected (7 days selected * 60 minutes selected * 96 rooms)

But if I select dates in another month, it keeps totaling to each month's time (October bar doesn't show up due to limited extract, but September should still only show 40320 minutes):

I attempted to utilize the running sum table calculation, but if the category does not have data for a particular day, it doesn't add the minutes for that selected day.

Any ideas?

• ###### 1. Re: How to calculate total minutes by month utilizing parameters and FIXED in calculation

I was able to find and combine several different things other people put together and seems to work for me, please see below:

//Number of Days Selected (+1 to account for the last day selected)

MAX({FIXED [Status], MONTH([Start Time]) :

DATEDIFF('day',

IF MONTH([Start Date]) < MONTH(MIN([Start Time]))

THEN DATETRUNC('month',MIN([Start Time]))

ELSEIF MONTH([Start Date]) = MONTH(MIN([Start Time]))

AND DAY([Start Date]) < DAY(MIN([Start Time]))

THEN [Start Date]

ELSE MIN([Start Time])

END,

IF MONTH([End Date]) > MONTH(MAX([Start Time]))

ELSEIF MONTH([End Date]) = MONTH(MAX([Start Time]))

AND DAY([End Date]) > DAY(MAX([Start Time]))

THEN [End Date] + 1

ELSE MAX([Start Time]) + 1

END)})

*

//Number of Minutes Selected

DATEDIFF('minute',MIN(DATETIME('1/1/2018 ' + [Parameters].[Start Time])),MIN(DATETIME('1/1/2018 ' + [Parameters].[End Time])))

*

//Number of Rooms that are selected after all filters (that were added to context) are applied

SUM({FIXED : COUNTD([RoomName]) })

1 of 1 people found this helpful