cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

YTD uptime using measures

Hi,

 

I have several equipment (wells) which should run 24hrs per day on various facilities.

To define the overall uptime for each facility, I'd like to know if at least 1 one of them has been running for 24 hrs.

I have a dataset where per item the running hours are recorded.

When using the following measure;

max_hrs_well = Calculate(max([running_hrs]),[facility_ID]),

this gives me for each the day the correct value. (See screenshot below)

 

However when I want to create a cumulitive of the hrs to track the performance YTD, doesn't 'just' add the current day with the previous day. I have tried sumx and maxx, but these formulations give me the original number.

 

Also I have created an additional if-measure, and then try to sum it with;

Cumulative = calculate (max_hrs_well, datesytd [ date])

But this does add the values on consecutive days.

 

While writing this post, I think I can also add a column to my dataset, but I'd rather implement a correct measure.

I have also searched the forum, but I feel like some solutions are just too complex.

 

GerlofVisser_1-1593088770750.png

Thanks in advance!

 

Gerlof

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: YTD uptime using measures

For anyone following this topic, I have solved it myself.

 

I added a column to my table which finds the maximum uptime of the wells based on a facility filter.

Then with the calculate function I create an average over this value.

Using the filter for the current year only, it shows the average YTD.

Not sure if this is the most robust solution, but it works.

 

If anyone is interested in more details of these explanation, just let me know.

 

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: YTD uptime using measures

@GerlofVisser , In datesyd and the date in the visual both should come from a date/calendar table.

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: YTD uptime using measures

Thanks for your quick answer @amitchandak , I tried answering yesterday, but an error occured...

 

Just to clarify a bit, I do have a date table and it's linked correctly, but I think the issue has to do with the max function.

This is my measure for checking whether the facility was up and running for 24hrs:

 
max hrs well = CALCULATE(MAX(energy_unit_data_fdc_value[hrs_online]),energy_unit_data_fdc_value[EU_ID])
 
Then for the cumulative I use the following function, but that doesn't seem to work.
Cumulative hrs online = CALCULATE([max_hrs_well], DATESYTD(Rolling_Calendar[Date])) also the adjustment
Cumulative hrs online = CALCULATE([max_hrs_well], DATESYTD(Rolling_Calendar[Date], "12/31")) does not work.
 
I have also tried to use a SUMX and MAXX, but this doesn't work either;
Uptime_Field = SUMX(energy_unit_data_fdc_value,MAXX(energy_unit_data_fdc_value,energy_unit_data_fdc_value[hrs_online]))/[Wellcount]
 
The wellcount (=amount of wells) statement is added, otherwise the value is the multiplication of the (amount of wells) times  "max_hrs_well"
 
Any advise is appreciated.
 
Regards,
Gerlof
Highlighted
Super User IV
Super User IV

Re: YTD uptime using measures

@GerlofVisser , Do you max of row level data or sum till month level and then do max . If you can explain by a data example



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: YTD uptime using measures

Hi, 

 

I have created a quick excel example, but this is what I want to achieve.

@amitchandak 

@OwenAuger , I have seen your posts, maybe you can help as well?

Hope that this helps.

GerlofVisser_0-1593168724871.png

 

Thanks!

Gerlof

 

Highlighted
Frequent Visitor

Re: YTD uptime using measures

Ok, I have created some additional measures and am trying to get it right, but I think the issue lies with summing up measures.

I have also tried to summarize the column I am creating in mutiple steps in one formula, this looks something like:

 

 

Uptime_Field = SUMX(
SUMMARIZE(energy_unit_data_fdc_value, Facility_Lookup[Facility_ID],"Max Hours", if((CALCULATE(max(energy_unit_data_fdc_value[hrs_online]),Facility_Lookup[Facility_ID]))=24 , 1 , (CALCULATE(max(energy_unit_data_fdc_value[hrs_online]),Facility_Lookup[Facility_ID]))/24)),[Max Hours])

 

 

My original approach was the following:

 

With this measure, I am finding the max of all the wells which belong to a certain facility ID, this gives me a good result.

 

max hrs well = CALCULATE(max(energy_unit_data_fdc_value[hrs_online]),flowID_Lookup[FACILITY_ID])

 

 

With the following measure I am normalizing the data on a daily basis

 

Uptime_Field_day = if([max hrs well]=24,1,[max hrs well]/24)

 

 

And this measure "Uptime_Field_Day" gives exactly the same results as the "Uptime_Field" measure.

 

When I then use the YTD function to calculate the cumulative, the results are the same.

 

Cumulative hrs online = CALCULATE([Uptime_Field_day], DATESYTD(Rolling_Calendar[Date]))

 

 

GerlofVisser_0-1593520580167.png

 

Highlighted
Frequent Visitor

Re: YTD uptime using measures

For anyone following this topic, I have solved it myself.

 

I added a column to my table which finds the maximum uptime of the wells based on a facility filter.

Then with the calculate function I create an average over this value.

Using the filter for the current year only, it shows the average YTD.

Not sure if this is the most robust solution, but it works.

 

If anyone is interested in more details of these explanation, just let me know.

 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors