Updating total hours for the year

[Anonymous]

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?  

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

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.