Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Updating total hours for the year

Hi all, 

 

I am currently creating a dashboard that will display the overall equipment efficiency, basically calculated by taking the downtime in comparison to available time. 

However, I would like my available time to be the total hours in the year to the current date instead of just always having the entire year, so that the OEE will not be much lower in the beginning of the year because its being divided by such a large available time.

 

Is there a way to create a measure that displays a sum of hours for the year that updates every day?  

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    From your description, I understand that your device runs every day of the year. Now you want to get if you have no value in your data table for that date and you want to return downtime = 0 and available time = 24 for that date. Is it right?

    I think you can try to create a date table and calculate the downtime and available time by dax. 

    Date = 
VAR _T =
    ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Downtime",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Downtime] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                0
            ),
        "Available time",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Available time] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                24
            )
    )
RETURN
    _T2

    Here I use the same sample like before.

    Then you can calculate percentage by the code above.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I think you want to calcualte the percentage by comparing downtime and available time.

    My Sample:

    Try this code.

    Compression Downtime and Availabletime = 
VAR _Downtime =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Year] = MAX ( 'Table'[Year] ), 'Table'[Date] <= TODAY () )
        ),
        'Table'[Downtime]
    )
VAR _Availabletime =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Year] = MAX ( 'Table'[Year] ), 'Table'[Date] <= TODAY () )
        ),
        'Table'[Available time]
    )
RETURN
    DIVIDE ( _Downtime, _Availabletime )

     Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Rico, 

     

    Thank you for your response. The calculation you have shared seems correct however my available time is every day of the year due to the available time of the equipment being 24/7, therefore I need the time to update daily by an additional 24 hours even if there is not any downtime on that date. 

     

    Are there any functions that have some sort of 'Hours to date' option? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      From your description, I understand that your device runs every day of the year. Now you want to get if you have no value in your data table for that date and you want to return downtime = 0 and available time = 24 for that date. Is it right?

      I think you can try to create a date table and calculate the downtime and available time by dax. 

      Date = 
VAR _T =
    ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Downtime",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Downtime] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                0
            ),
        "Available time",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Available time] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                24
            )
    )
RETURN
    _T2

      Here I use the same sample like before.

      Then you can calculate percentage by the code above.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.