cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roblu001
Resolver II
Resolver II

Multiple Windows of Time

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 NumberEquipment SerialInstall Date
1DeviceSN1November 1 2020
1DeviceSN2November 2 2020
2DeviceSN3February 5 2021
3DeviceSN4March 20 2021

 

Location Closure Table

LocationClose DateRe-Open Date
1December 27 2020March 8 2021
1April 7 2021May 8 2021
2December 27 2020March 8 2021
2April 7 2021May 8 2021
3April 10 2020May 1 2020
1 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

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

Hi  @roblu001 ,

 

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!

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

View solution in original post

amitchandak
Super User IV
Super User IV

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

Can you share sample output in table format?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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)

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors