Multiple Windows of Time

[Anonymous]

Hi All,

I recently discovered the SUMX function and I think its great! however, what I need to do is calculate the number of operating days for a specific piece of equipment. I've got tables below to demonstrate what I believe the data should look like. Ultimately, my end result will be to come up with a (filterable) number of days that each piece of equipment has been operational based on its install date and the open days of the location its installed in.

In my case, the first assumption is that I'm measuring only days since January 31st 2021. For all intents and purposes where locations have been shut down a second time, there will be no equipment installed in this duration of time. 

Equipment Table

Location Number	Equipment Serial	Install Date
1	DeviceSN1	November 1 2020
1	DeviceSN2	November 2 2020
2	DeviceSN3	February 5 2021
3	DeviceSN4	March 20 2021

Location Closure Table

Location	Close Date	Re-Open Date
1	December 27 2020	March 8 2021
1	April 7 2021	May 8 2021
2	December 27 2020	March 8 2021
2	April 7 2021	May 8 2021
3	April 10 2020	May 1 2020

[v-kelly-msft]

Hi  @Anonymous ,

For location1, Equipment Serial DeviceSN1,the total operation days is Date(2020,12,27)-date(2020,11,1) add Today()-Date(2021,3,8)，right?

If possible,can you pls provide a calculation formula or output in numerical calculation format?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

Hello,

I've been trying to reply but I'm getting errors.

I used a macro to stack the data, then using power automate to append per day. Now I have a table that has the date, location number, equipment serial, and 1/0 for open/closed per day. This solves all the problems I have for now

[amitchandak]

@Anonymous , the second table does not have any Equipment details. Then which record belongs to which?

Can you share sample output in table format?

[Anonymous]

The situation is that my workplace has purchased some equipment, this equipment needs to be measured against its daily performance (Units produced per day). However, some of the locations in which these units are installed have experienced closures and the organization has asked for the number of operating days to be adjusted to reflect the closures. We have a matrix of scenarios. Whatever the solution, I can apply it to only the current fiscal year (started January 31st) or I can apply it to since the equipment was installed.
Equipment Install Scenario
• Before January 31st
• After January 31st during closure
• After January 31st and after store has re-opened
Location Closure Scenario
• Location has had no closures this year
• Location has had 1 closure this year
• Location has had 2 closures this year
How I would calculate the math “manually” is:
• If the equipment was installed before the fiscal year, I would calculate the total operational days and then subtract all the closed days (from either 1 closure window or 2 closure windows)
• If the equipment was installed after the fiscal year but before the store re-opened, I would calculate the days since open (and then subtract any close days that may take place after the location has re-opened)
• If the equipment was installed after the location re-opened I would calculate days after the installation date (and then subtract any close days that may take place after the equipment is installed)

[Anonymous]

In this case, the second table would be linked to the first on the location number. I will get a sample output shortly.