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 li...
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.

     

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.