Forum Discussion

GerlofVisser's avatar
GerlofVisser
Helper I
6 years ago
Solved

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 run...
Need Help
  • GerlofVisser's avatar
    GerlofVisser
    6 years ago

    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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

  • GerlofVisser's avatar
    GerlofVisser
    Helper I
    6 years ago

    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
    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      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

      • GerlofVisser's avatar
        GerlofVisser
        Helper I
        6 years ago

        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.

         

        Thanks!

        Gerlof